Forum Discussion
Solved
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 Po...
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] ) )
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 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% |