Add custom Total row

[akhilduvvuru]

Hi, I have a requirement to add custom total row in Power BI table visual

Source Data:

DIM_TABLE

ID	Category
1	Apple
2	Orange
3	Fruits Total
4	Pen
5	Scale
6	Ink Pot
7	Stationary Total
8	Shirt
9	Pant
10	Clothing Total

FACT_TABLE

ID	SalesAmount
1	223
2	456
4	123
5	904
6	345
8	233
9	126

This is how my source data looks.

My expected output should look like below:

ID	Category	SalesAmount
1	Apple	223
2	Orange	456
3	Fruits Total	Sum of Apple (223) and Orange (456) = 679
4	Pen	123
5	Scale	904
6	Ink Pot	345
7	Stationary Total	Sum of Pen (123) and Scale (904) and Ink Pot (345) = 1372
8	Shirt	233
9	Pant	125
10	Clothing Total	Sum of Shirt (233) and Pant (126) = 679

I wrote a measure to achieve this as below:

Measure = IF(MAX(DIM_TABLE[CATEGORY]) = "Fruits Total", CALCULATE(SUM(FACT_TABLE[SALESAMOUNT]), FACT_TABLE[CATEGORY] = "APPLE") + CALCULATE(SUM(FACT_TABLE[SALESAMOUNT]), FACT_TABLE[CATEGORY] = "ORANGE"), SUM(FACT_TABLE[SALESAMOUNT]))

-- Explination: IF my Category is "Fruits Total" then I want to add APPLE sales amount and Orange sales amount and show in Fruits Total. Else I want to show SUM(SalesAmount) 

IF I use this measure in card, it is showing 679 which is correct. But if I use the same measure in a table visual next to DIM_TABLE[CATEGORY] column, it is blank. Not showing anything.

Can someone please help me with the same? Thanks!

[jdbuchanan71]

That would be like this.

Sales with total = 
VAR _Category = SELECTEDVALUE ( DIM_TABLE[Category] )
VAR _Fruits =       CALCULATE (
                        SUM ( FACT_TABLE[SalesAmount] ),
                        REMOVEFILTERS ( DIM_TABLE ),
                        DIM_TABLE[Category] IN { "Apple", "Orange" } )
VAR _Stationary =   CALCULATE (
                        SUM ( FACT_TABLE[SalesAmount] ),
                        REMOVEFILTERS ( DIM_TABLE ),
                        DIM_TABLE[Category] IN { "Pen", "Scale", "Ink Pot" } )
VAR _Clothing =     CALCULATE (
                        SUM ( FACT_TABLE[SalesAmount] ),
                        REMOVEFILTERS ( DIM_TABLE ),
                        DIM_TABLE[Category] IN { "Shirt", "Pant" } )
RETURN
    SWITCH (
        TRUE (),
        _Category = "Fruits Total", _Fruits,
        _Category = "Stationary Total", _Stationary,
        _Category = "Clothing Total",   _Clothing,
        _Category = "Total %", FORMAT ( DIVIDE (_Fruits - _Stationary, _Fruits ), "Percent" )
        SUM ( FACT_TABLE[SalesAmount] )
    )

[jdbuchanan71]

Since we will be using some of the numbers multiple times I would move them into variables like this.

Sales with total = 
VAR _Category = SELECTEDVALUE ( DIM_TABLE[Category] )
VAR _Fruits =       CALCULATE (
                        SUM ( FACT_TABLE[SalesAmount] ),
                        REMOVEFILTERS ( DIM_TABLE ),
                        DIM_TABLE[Category] IN { "Apple", "Orange" } )
VAR _Stationary =   CALCULATE (
                        SUM ( FACT_TABLE[SalesAmount] ),
                        REMOVEFILTERS ( DIM_TABLE ),
                        DIM_TABLE[Category] IN { "Pen", "Scale", "Ink Pot" } )
VAR _Clothing =     CALCULATE (
                        SUM ( FACT_TABLE[SalesAmount] ),
                        REMOVEFILTERS ( DIM_TABLE ),
                        DIM_TABLE[Category] IN { "Shirt", "Pant" } )
RETURN
    SWITCH (
        TRUE (),
        _Category = "Fruits Total", _Fruits,
        _Category = "Stationary Total", _Stationary,
        _Category = "Clothing Total",   _Clothing,
        _Category = "Total %", _Fruits - _Stationary & " (" & FORMAT ( DIVIDE (_Fruits - _Stationary, _Fruits ), "Percent" ) &")",
        SUM ( FACT_TABLE[SalesAmount] )
    )

Not sure if this is what you meant by "both (number and percentage)" so you may have to adjust it a bit.

[akhilduvvuru]

@jdbuchanan71 - Sorry if I miss lead you. What I mean is, in the same measure (Sales with total) I want to show numbers and percentages. But not in a same row, different row based on Category. Can you please help me with this?
Expected output

Category	Sales with total
Apple	223
Orange	456
Fruits Total	679
Pen	123
Scale	904
Ink pot	345
Stationary Total	1372
Shirt	233
Pant	126
Clothing Total	359
Total %	-102.06%

[akhilduvvuru]

 - On top of it I also have a case where there is ID 11 which is Total % where I have to calculate (Fruits Total - Stationary Total)/Fruits Total 

(679-1372)/679 = -103.12%

Along with your measure (Sales with total), I should also include this and show both (number and percentage) together. It would be great if you help on the same. Thanks!

@jdbuchanan71

[jdbuchanan71 · Accepted solution]

@akhilduvvuru 

We have to break the link when calculating the total which we can do using REMOVEFILTER like this.

Sales with total = 
VAR _Category = SELECTEDVALUE ( DIM_TABLE[Category] )
RETURN
    SWITCH (
        TRUE (),
        _Category = "Fruits Total",     CALCULATE (
                                            SUM ( FACT_TABLE[SalesAmount] ),
                                            REMOVEFILTERS ( DIM_TABLE ),
                                            DIM_TABLE[Category] IN { "Apple", "Orange" } ),
        _Category = "Stationary Total", CALCULATE (
                                            SUM ( FACT_TABLE[SalesAmount] ),
                                            REMOVEFILTERS ( DIM_TABLE ),
                                            DIM_TABLE[Category] IN { "Pen", "Scale", "Ink Pot" } ),
        _Category = "Clothing Total",   CALCULATE (
                                            SUM ( FACT_TABLE[SalesAmount] ),
                                            REMOVEFILTERS ( DIM_TABLE ),
                                            DIM_TABLE[Category] IN { "Shirt", "Pant" } ),
        SUM ( FACT_TABLE[SalesAmount] )
    )

[SUMESHKUMAR22]

Hi @jdbuchanan71 ,
In the sales total measure you have manuaaly passed the values in {"} instead is there's  a way to dynamically pick the values based on category. Because in my case the category will remain same but the values inside the category will change over the time as its not static.
E.g.
In below image, department name is category & Resource name are the values inside that. Also I want to rename the row header names for Totals .
NOTE: I have to level of drill down in column & rows.

Thanks in advance!

[akhilduvvuru]

Thanks @jdbuchanan71 !