cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
giorajo
Helper I
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:

YearValueRatioResult
2018240% .8
2019115%5.5
20201520%3
Overall2833.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

@giorajo 

 

See if this works for you. First the model:

model.JPG

 

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:

result.JPG

 

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.






View solution in original post

8 REPLIES 8
giorajo
Helper I
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. 

@giorajo 

 

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.






@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.

@giorajo 

 

See if this works for you. First the model:

model.JPG

 

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:

result.JPG

 

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.






View solution in original post

Thank you very much, 

@PaulDBrown !!! This works!

 

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

PaulDBrown
Super User II
Super User II

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

 

 





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.






ziyabikram96
Resolver I
Resolver I

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

Yeah, from a separate date table.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors