'Reversing' Total Calculation in Measure

[giorajo]

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!

[giorajo]

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

[PaulDBrown]

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

[giorajo]

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

[PaulDBrown · Accepted solution]

@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

[giorajo]

Thank you very much, 

@PaulDBrown !!! This works!

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

[PaulDBrown]

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

[ziyabikram96]

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

[giorajo]

Yeah, from a separate date table.