Forum Discussion
Add custom Total row
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!
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] ) )
7 Replies
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] ) )Thanks jdbuchanan71 !
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!
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.
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 outputCategory 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%
- 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!
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] ) )
