Forum Discussion

Frequent Visitor

1 year ago

Solved

Create measure that replicates SUMIF/matrix aggregation

I am sure that there is an easy way to do this but every way I try does not produce the intended result. I have a table with a large variety of categories and a number of columns with values. Unfortu...

Need Help

SamWiseOwl
1 year ago
Hi Y1009666

% of something =

VAR SYTD = SUM('Table'[YTD])

VAR FYTD = SUM('Table'[Final])

RETURN

SWITCH(

TRUE()

,ISINSCOPE('Table'[Level 5]) && SYTD > 0 --if on the 5 row and has sales

,SUM('Table'[YTD])/SUM('Table'[Final])

,ISINSCOPE('Table'[Level 4]) && SYTD <> 0 || ISFILTERED('Table'[Level 5]) --row 4 and sales or level 5

,CALCULATE(SUM('Table'[YTD])/SUM('Table'[Final]),ALLSELECTED('Table'[Level 5]))

,ISINSCOPE('Table'[Level 3]) && SYTD <> 0 || ISFILTERED('Table'[Level 4])

,CALCULATE(SUM('Table'[YTD])/SUM('Table'[Final]),ALLSELECTED('Table'[Level 4]))

,ISINSCOPE('Table'[Level 2]) && SYTD <> 0 || ISFILTERED('Table'[Level 3])

,CALCULATE(SUM('Table'[YTD])/SUM('Table'[Final]),ALLSELECTED('Table'[Level 3]))

,ISINSCOPE('Table'[Level 1]) && SYTD <> 0 || ISFILTERED('Table'[Level 2])

,CALCULATE(SUM('Table'[YTD])/SUM('Table'[Final]),ALLSELECTED('Table'[Level 2]))

)