Search code examples
powerbidax

In Power BI, how to sum a column that is filtered by DAX measure?

Say I have a sales table:

product product_category sales
a 1 10
b 1 20
c 2 10
d 1 15
e 1 20
f 2 15
g 2 10
h 3 25
i 3 30
j 1 15
k 2 25
l 2 10
m 1 5
n 2 25
o 2 10
p 3 20

*Edit: Seems the md renderer is broken, so I attach an image instead: enter image description here

I would like to classify the product as good and bad products according to selected product category filter. Then, I need to calculate the average sales of bad products and good products respectively. Here is my current visual:
Power BI Table Visual

These are the logic and DAX of the measures:

avg_sales = CALCULATE(AVERAGE('Table'[sales]),ALLSELECTED())
index = sum('Table'[sales])/[avg_sales]
indicator = IF([index]>1,"Good","Bad")
bad_sales = IF([indicator]="Bad",SUM('Table'[sales]))

The ALLSELECTED() filter refers to the slicer of product category.

The question is how to calculate the average of bad sales?

I have tried to use the calculate and filter combo, but seems like we cannot filter by measure. Also, as the real report consists of not only product category slicer, it is impossible to do the avg_sales for every combination of filterings before importing to Power BI. Thanks in advance.

Solution

  • You can try:

    AVERAGEX( ALLSELECTED('Table'), [bad_sales] ) )

    You can filter on Measure if use FILTER. For example:

    COUNTROWS(FILTER('Table', [indicator] = "Bad"))