Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community
I have a question that I am struggling with.
I must calculate a new total for a column using a formula.
At the moment the row calculations is based on: ( This gives me the output I want )
=FABS(RangeSum(if(vMeasure='Tonnage',Sum({<[Actual Units Tonnage] = {">0"}>}[Actual Units Tonnage]),sum({<[Actual Units] = {">0"}>}[Actual Units])))
- if(vMeasure='Tonnage',Sum({< Data ={'Forecast Tonnage'}>} Amount),Sum({< Data ={'Forecast'}>} Amount)))
However I want to divide the above by the total of the rows therefore I need a syntax to calculate the total and I am not getting that right
Please can I ask help
Thanks
To calculate the total, you need to have the 'total' qualifier inside the aggregation function. So, you should probably divide by
FABS(RangeSum(if(vMeasure='Tonnage',Sum({<[Actual Units Tonnage] = {">0"}>} total [Actual Units Tonnage]),sum({<[Actual Units] = {">0"}>} total [Actual Units])))
- if(vMeasure='Tonnage',Sum({< Data ={'Forecast Tonnage'}>} total Amount),Sum({< Data ={'Forecast'}>} total Amount)))
To calculate the total, you need to have the 'total' qualifier inside the aggregation function. So, you should probably divide by
FABS(RangeSum(if(vMeasure='Tonnage',Sum({<[Actual Units Tonnage] = {">0"}>} total [Actual Units Tonnage]),sum({<[Actual Units] = {">0"}>} total [Actual Units])))
- if(vMeasure='Tonnage',Sum({< Data ={'Forecast Tonnage'}>} total Amount),Sum({< Data ={'Forecast'}>} total Amount)))
Thanks for the reply. I understand.
I managed the following with positive results:
1-(if(Dimensionality ()=1,FABS(RangeSum(if(vMeasure='Tonnage',Sum({<[Actual Units Tonnage] = {">0"}>}[Actual Units Tonnage]),sum({<[Actual Units] = {">0"}>}[Actual Units])))
- if(vMeasure='Tonnage',Sum({< Data ={'Forecast Tonnage'}>} Amount),Sum({< Data ={'Forecast'}>} Amount))),Sum(FABS(aggr((if(vMeasure='Tonnage',Sum({<[Actual Units Tonnage] = {">0"}>}[Actual Units Tonnage]),sum({<[Actual Units] = {">0"}>}[Actual Units])))
- if(vMeasure='Tonnage',Sum({< Data ={'Forecast Tonnage'}>} Amount),Sum({< Data ={'Forecast'}>} Amount)),Brand))))
/
if(vMeasure='Tonnage',Sum({< Data ={'Forecast Tonnage'}>} Amount),Sum({< Data ={'Forecast'}>} Amount)))
Regards