Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: 'Reversing' Total Calculation in Measure

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

'Reversing' Total Calculation in Measure

01-18-2021
03:13 AM

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 **Overal**l 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!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2021
07:15 AM

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**

Proud to be a Super User!

Paul on Linkedin.

8 REPLIES 8

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2021
04:12 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2021
04:32 AM

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]

Proud to be a Super User!

Paul on Linkedin.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2021
05:51 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2021
07:15 AM

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**

Proud to be a Super User!

Paul on Linkedin.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2021
07:51 AM

Thank you very much,

@PaulDBrown !!! This works!

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2021
03:48 AM

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

Proud to be a Super User!

Paul on Linkedin.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2021
03:27 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2021
04:04 AM

Yeah, from a separate date table.

Announcements

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

75840 | |

58501 | |

49266 | |

33570 | |

22596 |

Top Kudoed Authors

User | Count |
---|---|

460 | |

284 | |

78 | |

76 | |

59 |