Forum Discussion

giorajo's avatar
giorajo
Helper I
5 years ago
Solved

'Reversing' Total Calculation in Measure

Hello,   I am hoping someone can help me with my question below:    I got two tables - Value table and Ratio table.   Value table looks like these:   Date   Value 1/1/2020   10 2...
Need Help
Show and Tell
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    giorajo 

     

    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's avatar
PaulDBrown
Community Champion
5 years ago

giorajo 

Try:
FInal result = SUMX(Table, [Result])
(Where 'Table' is the table you are using for the YEAR field in the visual)

 

You can then use this measure to calculate the ratios, including for the total and use it in the visual instead of the Ratio field:

Total Ratio = DIVIDE([Final result], [Sum of value])