Add a Difference Row for a group

[ak77]

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

[Anonymous · Accepted solution]

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ak77]

Hi @Anonymous , Thanks for reply

Is this possible with Table Vizualization? Can you please help

[ak77]

@Anonymous , Please help if possible on this

[Anonymous]

Hi @ak77 ，

As far as I know, Power BI doesn't support us to show results in hierarchy level. Matrix visual should be a better way.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[foodd]

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.