Filtering out all items based on one value in a vertical table

[Trisulara88]

Hello all,

this is my first post and I hope I can express myself correctly 🙂

I have a table containing different sales KPI's on a product level with the following columns:

Product | Date | KPI | value

The KPI column includes the KPIs revenue, weighted distribution and units sols. The value column contains the value of the different KPIs for a given product on a given date.

I now have a visual (Zebra BI table - see screenshot) showing the three KPIs for current year, previous year and the deviation. In the visual I now only want to show the products where the weighted distribution KPI is > 10. All the approaches I tried give me a result where all other KPIs except "weighted distribution" are filtered out.

Is this possible using a measure or a clever slicer setting? I would like to avoid using an aditional column model since my table already contains > 20 million rows and is steadily increasing. If it is not possible without an additional column, I'm also glad about help with this.

Screenshot of visual

Thank you in advance 🙂

Trisulara

[Trisulara88]

Thank you for your answer. I see that I can filter out products where the weighted distribution is >10. Its not quite the result I would like to achieve though. In your example table, I would like to also show the other KPIs for product A. If I use your measure it filters out the other KPI and only shows the weighted distribution. I would like to display revenue and units sold as well. Thank you 🙂

[Anonymous]

Hi @Trisulara88 ,

Provide some test data and screenshots of the desired results so that I can answer your question as soon as possible.

Best Regards,
Adamk Kong

[Trisulara88]

Hey Adamk,

here is the sample data: https://we.tl/t-EEPfPEO0k2 

he original dataset is a lot larger though. 

And here are two screenshots of the desired result. Ideally I want to use a slicer to filter out the products with a weighted distribution > 10. In the first screenshot the filter selection is not active. In the second screenshots the products are filtered out.

 Note: if I want to filter a different date, only those products which have a weighted distribution > 10for that date should be filtered out.

Hope this works 🙂

[Trisulara88]

No ideas? 😔 or not possible?

[Anonymous]

Hi @Trisulara88 ,

You can try to create measure like below, it shows the products where the weighted distribution KPI is > 10.

WeightedDistribution_GT_10 = 
CALCULATE(
    SUMX(
        FILTER(
            VALUES(Sales[Product]),
            CALCULATE(
                MAX(Sales[value]),
                Sales[KPI] = "weighted distribution"
            ) > 10
        ),
        1
    ),
    ALL(Sales[KPI])
)

If it's not what you want, please provide test data and screenshots of the desired results.

Best Regards,
Adamk Kong

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