Forum Discussion
adding variance into table
is there a way to add variance between current and prior into the table?
current and prior data are in the same table but I am not sure how to add the variance data.
Thank you,
| type | group | group2 | color | name | value1 | value2 | value3 | value4 | value5 |
| current | aaa | group name1 | color name12 | name3 | 5 | 5 | 5 | 5 | 6 |
| current | ccc | group name2 | color name13 | name4 | 5 | 5 | 5 | 5 | 8 |
| current | bbb | group name3 | color name14 | name5 | 5 | 6 | 7 | 8 | 6 |
| current | ddd | group name4 | color name15 | name6 | 5 | 5 | 9 | 5 | 8 |
| current | ccc | group name5 | color name16 | name7 | 9 | 8 | 5 | 5 | 6 |
| current | bbb | group name6 | color name17 | name8 | 9 | 9 | 5 | 5 | 6 |
| current | ddd | group name7 | color name18 | name9 | 5 | 5 | 10 | 5 | 6 |
| current | ccc | group name8 | color name19 | name10 | 8 | 5 | 5 | 44 | 6 |
| current | bbb | group name9 | color name20 | name11 | 7 | 5 | 5 | 55 | 33 |
| current | ddd | group name10 | color name21 | name12 | 6 | 5 | 5 | 5 | 6 |
| prior | aaa | group name1 | color name12 | name3 | 2 | 3 | 4 | 5 | 6 |
| prior | ccc | group name2 | color name13 | name4 | 3 | 2 | 5 | 4 | 9 |
| prior | bbb | group name3 | color name14 | name5 | 4 | 1 | 6 | 3 | 12 |
| prior | ddd | group name4 | color name15 | name6 | 5 | 0 | 7 | 2 | 15 |
| prior | ccc | group name5 | color name16 | name7 | 6 | -1 | 8 | 1 | 18 |
| prior | bbb | group name6 | color name17 | name8 | 7 | -2 | 9 | 0 | 21 |
| prior | ddd | group name7 | color name18 | name9 | 8 | -3 | 10 | -1 | 24 |
| prior | ccc | group name8 | color name19 | name10 | 9 | -4 | 11 | -2 | 27 |
| prior | bbb | group name9 | color name20 | name11 | 10 | -5 | 12 | -3 | 30 |
| prior | ddd | group name10 | color name21 | name12 | 11 | -6 | 13 | -4 | 33 |
| variance | |||||||||
| variance | |||||||||
| variance | |||||||||
| variance | |||||||||
| variance | |||||||||
| variance | |||||||||
| variance | |||||||||
| variance | |||||||||
| variance | |||||||||
| variance |
Hi topazz11
Instead of adding extra rows to your data and potentially slow down refresh and increase your semantic model's footprint, use calculation groups instead so the same logic is applied to the measures added to a visual. Please see the attached sample pbix.
7 Replies
Hi topazz11
Instead of adding extra rows to your data and potentially slow down refresh and increase your semantic model's footprint, use calculation groups instead so the same logic is applied to the measures added to a visual. Please see the attached sample pbix.
Thank you, this works. I have never used this before. how does it filter current, prior or variance without relationship? it is mystery..
Hi topazz11
If your goal is to display Current, Prior, and Variance on the same page and switch between them dynamically using a slicer, then the cleanest and most scalable solution is to use Calculation Groups via Tabular Editor. This lets you define one set of logic for your measures, and simply toggle between views without duplicating data or adding extra rows.
It keeps your model lean, improves performance, and gives end users a smooth slicer-based experience.
That said, if you still need to physically calculate variance rows (for audit, export, or other use cases), you can absolutely do that by:
-
Grouping by a common key (like name + group),
-
Joining current and prior rows in Power Query or DAX, and
-
Calculating the variance as Current - Prior.
yes, please guide me.
-
Question: Why do you want to have the Variance on additional ROWS, not as calculated a Column? Better yet, a set of MEASURES for SUM (Current}, SUM (Prior). Then Variance is just another measure
= SUM(Current) - SUM(Prior)
Putting Variance on its own set of rows does not seem logical. Do you have a pressing need to do it that particular way? If so, I would be curious to know the reason.
I'm trying to display tables in one page using a slicer, allowing users to select 'Current', 'Prior', or 'Variance' and display the corresponding page based on their selection
I am trying to add all in one page and have them change dynamically based on the slicer.
