0

I am creating vizs in QS and need help with calculations. It is easier for me using PowerBI and DAX which I am more familiar with and can use formulas like ALL, FILTER, Sameperiodlastyear etc to get the results I need, but unfortunately are not available in QS or a way to translate them to equivalent QS formulas.

For example I need to make the sum of a numerical column static rather than the default dynamic aggregation and use as denominator in value calculations by groups Tried the sumover function but the Prefilter, preagg and postaggfilter calculation levels are still confusing.

I have attached sample data

These are some of the calculations I need to do and repeated for the other dimensions:

I have tried different solutions but the results appear wrong on checking

Total Amount by year,

also by quarter

Total Quantity by year,

also by quarter

Companyshareamount by year= Amount by Companyandyear/ Total amount for that Year

Company amount PerYear

companyshareperyear percentage

companytransamountperQt_Yr

transamountperQt_Yr

companyshareperQt_Yr

?Year = extract(‘YYYY’, date) coming as a number

?Quarter= extract(‘Q’, date) tried to use in the sumover function but getting an error

?companyshare= sumOver(sum(amount),[company], POST_AGG_FILTER)/sumOver(sum(amount), )

the documentation says no aggregation with pref or preagg

?companyshareamountperQt for selected year = CompanyamountperQt/totalamountperQt

Company share for selected year- share for previous year

I have tried creating year filters and parameters to pass into the formulas to group or partition by year

Kindly help. Thanks in Advance