Forum Discussion

Trisulara88's avatar
Trisulara88
Frequent Visitor
2 years ago

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

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

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

     

  • Trisulara88's avatar
    Trisulara88
    Frequent Visitor
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Trisulara88
        Frequent Visitor
        2 years ago

        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 🙂