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 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%
