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
PaulDBrown I forgot to mention that there is a year slicer. And the value the should be computed will be the sum of all the value less than or equal to current year. So if the value of selected year is 2020 then the Sum of Value, Ratio and Sum of Amount will be 28, 32.21 and 9.3 respectively.
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