Forum Discussion
Add a Difference Row for a group
Hi All,
i have following requirement: Pic 1 is the table source and QTD,YTD and Calendar 1 yr measures are calculated . The User wants to add a Row "Difference" for Each group (A,Abenchmark),(B,Bbenchmark) and calclate the difference as in Fig 2. Please help to transpose this
Pic 1:
| Header | QTD | YTD | Calendar 1 yr |
| A | 1 | 30 | 5 |
| Abenchmark | 2 | 20 | 6 |
| B | 3 | 5 | 3 |
| Bbenchmark | 4 | 15 | 7 |
| C | 5 | 23 | 3 |
| Cbenchmark | 6 | 44 | 4 |
| D | 7 | 3 | 2 |
| Dbenchmark | 8 | 17 | 1 |
Pic 2:
| Header | QTD | YTD | Calendar 1 yr |
| A | 1 | 30 | 5 |
| Abenchmark | 2 | 20 | 6 |
| Difference | -1 | 10 | -1 |
| B | 3 | 5 | 3 |
| Bbenchmark | 4 | 15 | 7 |
| Difference | -1 | -10 | -4 |
| C | 5 | 23 | 3 |
| Cbenchmark | 6 | 44 | 4 |
| Difference | -1 | -21 | -1 |
| D | 7 | 3 | 2 |
| Dbenchmark | 8 | 17 | 1 |
| Difference | -1 | -14 | 1 |
Hi ak77 ,
Due to I don't know your data model, I create a sample to have a test.
Here I suggest you to create a Header Group table and create a relationship between it with your Fact table.
QTY with difference = IF ( HASONEVALUE ( 'Header Group'[Header] ), [M_QTY], CALCULATE ( [M_QTY], 'Header Group'[Sort] = 1 ) - CALCULATE ( [M_QTY], 'Header Group'[Sort] = 2 ) )YTD with difference = IF ( HASONEVALUE ( 'Header Group'[Header] ), [M_YTD], CALCULATE ( [M_YTD], 'Header Group'[Sort] = 1 ) - CALCULATE ( [M_YTD], 'Header Group'[Sort] = 2 ) )Calendar 1 yr with difference = IF ( HASONEVALUE ( 'Header Group'[Header] ), [M_Calendar 1 yr], CALCULATE ( [M_Calendar 1 yr], 'Header Group'[Sort] = 1 ) - CALCULATE ( [M_Calendar 1 yr], 'Header Group'[Sort] = 2 ) )Then create a Matrix visual > Turn of Stepped layout > Turn on the Per row level
>Turn of show subtotal for row level group > Change the subtotal name for row level Header.
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi ak77 ,
Due to I don't know your data model, I create a sample to have a test.
Here I suggest you to create a Header Group table and create a relationship between it with your Fact table.
QTY with difference = IF ( HASONEVALUE ( 'Header Group'[Header] ), [M_QTY], CALCULATE ( [M_QTY], 'Header Group'[Sort] = 1 ) - CALCULATE ( [M_QTY], 'Header Group'[Sort] = 2 ) )YTD with difference = IF ( HASONEVALUE ( 'Header Group'[Header] ), [M_YTD], CALCULATE ( [M_YTD], 'Header Group'[Sort] = 1 ) - CALCULATE ( [M_YTD], 'Header Group'[Sort] = 2 ) )Calendar 1 yr with difference = IF ( HASONEVALUE ( 'Header Group'[Header] ), [M_Calendar 1 yr], CALCULATE ( [M_Calendar 1 yr], 'Header Group'[Sort] = 1 ) - CALCULATE ( [M_Calendar 1 yr], 'Header Group'[Sort] = 2 ) )Then create a Matrix visual > Turn of Stepped layout > Turn on the Per row level
>Turn of show subtotal for row level group > Change the subtotal name for row level Header.
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
How to Get Your Question Answered Quickly meaning please add your work-in-progress Power BI Desktop file, and data source in Excel format. Please format any DAX measures with short lines for the benefit of the reader.
