cancel
Showing results for
Search instead for
Did you mean:  Helper I

## '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/1/2019 4 1/1/2018 2 2/1/2020 5 3/1/2019 7

While Ratio table contains these columns

 Date Ratio 1/1/2020 20% 1/1/2019 50% 1/1/2018 40%

Note that Ratio table only has one record per year and is always January 1.

I got this table visual that combines the 2 tables:

 Year Value Ratio Result 2018 2 40% .8 2019 11 5% 5.5 2020 15 20% 3 Overall 28 33.21% 9.3

I need to create measures for Overall  Value, Ratio and Result. I am having problems because Result is computed Value multiplied Ratio, but when computing the totals, Overall Ratio is calculated as Result divided by Value.

The user will select a year. so for 2020 the result will be 28, 33.21 and 9.3 for Value, Ratio and Total. If the selected year is 2019 then the result should be 13, 65.38% and 8.5.

Thank you in advance!

1 ACCEPTED SOLUTION  Super User II

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

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.

8 REPLIES 8  Helper I

@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.  Super User II

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]

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.  Helper I

@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.  Super User II

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

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.  Helper I

Thank you very much,

@PaulDBrown !!! This works!

I appreciate all your help and the effort you put into solving my problem!  Super User II

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])

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.  Resolver I

Where is the Year value coming from in the final table visual? From a separate date table?  Helper I

Yeah, from a separate date table.  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group #### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings. #### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (13,586)