Forum Discussion
'Reversing' Total Calculation in Measure
See if this works for you. First the model:
1) measure for sum of values
Sum Values = SUM(FactTable[Values])2) Measure for the ratios (applied to each year)
Avg Ratio = CALCULATE(AVERAGE(Ratio[Ratio]), ALLEXCEPT('Date Table','Date Table'[Year]))3) The result when multiplying by the ratio:
Result = SUMX('Date Table', [Sum Values]*[Avg Ratio])4) The resulting ratio (correct total)
Resulting Ratio = DIVIDE([Result], [Sum Values])5) Running total for values
Running total values = CALCULATE([Sum Values], FILTER(ALL('Date Table'), 'Date Table'[Date] <= MAX('Date Table'[Date])))'8) Running total of results
Running total result = CALCULATE([Result], FILTER(ALL('Date Table'[Date]), 'Date Table'[Date] <= MAX('Date Table'[Date])))9) Finally, the ratio resulting from the running totals
Running total ratio = DIVIDE([Running total result], [Running total values])which gets you this:
Attached is the sample PBIX file
Sorry, I'm confused. The final visual you show calculates the [result] on a row by row basis (for each year) correct?
When you now say you want the cumulative calculation, I take it that you mean that the [result] value is still the product year by year, and then yo wish to calulate the running total, which means the ratio is calculated based on this running total also?
If so you will need new measures:
1) Running total for Value = CALCULATE([Sum of value], FILTER(ALL(Date table), Date table [Year] <= MAX(Date table [Year]))
2) Running total for Result = CALCULATE([Result], FILTER(ALL(Date table), Date table [Year] <= MAX(Date table [Year]))
3) Final ratios = DIVIDE([Running total for Value], [Running total for Result]
PaulDBrownthank you for replying.
I actually tried the DAX statement you provided but it yielded wrong result.
On Year level we can simply get Result as Value * Ratio, but on Overall level, we have to 'reverse' the calculation as Ratio = Result divided by Value.
I apologize if I am not very clear.
See if this works for you. First the model:
1) measure for sum of values
Sum Values = SUM(FactTable[Values])2) Measure for the ratios (applied to each year)
Avg Ratio = CALCULATE(AVERAGE(Ratio[Ratio]), ALLEXCEPT('Date Table','Date Table'[Year]))3) The result when multiplying by the ratio:
Result = SUMX('Date Table', [Sum Values]*[Avg Ratio])4) The resulting ratio (correct total)
Resulting Ratio = DIVIDE([Result], [Sum Values])5) Running total for values
Running total values = CALCULATE([Sum Values], FILTER(ALL('Date Table'), 'Date Table'[Date] <= MAX('Date Table'[Date])))'8) Running total of results
Running total result = CALCULATE([Result], FILTER(ALL('Date Table'[Date]), 'Date Table'[Date] <= MAX('Date Table'[Date])))9) Finally, the ratio resulting from the running totals
Running total ratio = DIVIDE([Running total result], [Running total values])which gets you this:
Attached is the sample PBIX file
Thank you very much,
PaulDBrown !!! This works!
I appreciate all your help and the effort you put into solving my problem!