Forum Discussion

FloKlupp's avatar
FloKlupp
Frequent Visitor
5 years ago
Solved

Filter on a lower aggregation level than my visualization is

Hello all,

I have been working with Power BI since the beginning of 2021. And I am accordingly still a beginner.
I have adopted many reports and i am in the process of successively developing them. One request was the following (The technical topic is not quite correct. I just tried to present it simply).
As in the following example, currently all products are simply counted for each product group. But now only those should be counted that have been sold more than 3 times. But in my example there is no quantity. The rows must be counted (with count rows). Below I insert a screenshot with the expected (and my actual) result:

 

 

My problem is that I don't want to filter the data in the aggregation stage of the visualization. I want to filter them already in the step before. With the aggregated data is not available in any table. I want all of that to be done by Power BI (if that is even possible). The products should not appear in the actual report. The data is then used to calculate a percentage, for example.
Should this not be possible in the form of measures, this would also help me as a hint. Then I would have to take a closer look at the data model again, which I actually did not want to do in this step.

 

Further notice:
My real problem is even more complex as there are other dimensions above the product group. This causes my row counting to not count the correct dimension. I just want to achieve that I filter a dimension and only then aggregate it (This is not the visualized dimension and possibly 3 levels below).

 

This is my first entry here in the forum and I hope that my problem is clear. I would be happy if you could help me further. Otherwise feel free to contact me if you have any further questions.

I also attach the two tables:

 

ProductPrice
A15,00 €
A15,00 €
A15,00 €
A27,00 €
A27,00 €
A27,00 €
A27,00 €
A32,00 €
A32,00 €
B116,00 €
B116,00 €
B116,00 €
B116,00 €
B225,00 €
B225,00 €
B225,00 €
B225,00 €
B32,00 €

 

ProductProduct group
A1A
A2A
A3A
B1B
B2B
B3B

 

Greetings and thanks,
Florian

  • FloKlupp , Assume both tables are related. Create a measure like

     

    countx(filter(summarize(Product, product[product group], product[product], "_1", count(Sales[product])), [_1] >3),[Product])

2 Replies

  • FloKlupp , Assume both tables are related. Create a measure like

     

    countx(filter(summarize(Product, product[product group], product[product], "_1", count(Sales[product])), [_1] >3),[Product])

    • FloKlupp's avatar
      FloKlupp
      Frequent Visitor

      Hi amitchandak 

      Thanks a lot ! Was a bit more complicated, because of my datamodel. But i got it with the way you have shown.