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] ) )
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!
