topic calculate subtotal percentage in a matrix without considering blank cells in DAX Commands and Tips

calculate subtotal percentage in a matrix without considering blank cells

lawada — Wed, 01 Dec 2021 10:29:41 GMT

i have a matrix that shows percentage per each column and row :

rows: current month

columns: difference between current month and previous month

values: measure[percentage of returned users]

i want to find the average row per each column but without considering any blank months .

current output

for example , for column 63 output should be (20.93+16.67+20.59+12.50+23.33)/5=18.8%

i want to show this output in the subtotal of matrix and create a measure for it to show it in a line chart

Re: calculate subtotal percentage in a matrix without considering blank cells

amitchandak — Wed, 01 Dec 2021 15:23:51 GMT

lawada , ideally you should do is using allselected or removefilters

calculate(Averagex(Values(Table[Rows column]) , [percentage of returned users]), removefilter(Table[Rows column]))

Re: calculate subtotal percentage in a matrix without considering blank cells

lawada — Thu, 02 Dec 2021 08:38:34 GMT

thank you the measure worked out , is there a way i can get same result for subtotal field in the matrix? as when i enabled the subtotal for the rows, im getting the average for all cells including blank cells

Re: calculate subtotal percentage in a matrix without considering blank cells

Icey — Thu, 23 Dec 2021 03:08:01 GMT

Hi lawada ,

Try this:

calculate(Averagex(Values(Table[Rows column]) , CALCULATE( [percentage of returned users]))

I create a simple sample, not the same, just for reference. Please check if it works for you.

Measure 1 = AVERAGEX ( VALUES ( 'Table'[Category] ), CALCULATE ( SUM ( 'Table'[Value] ) ) )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.