Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter is not working with All SELECTED

Hi All,

I need your help with dax powerbi. I have difficulty solving for this hours but couldnt solve
I have measure which divides the sum of sku/total sum of sku to get the percentage.

Measure
SKU Stock Contribution (%) = DIVIDE(
SUM(Query1[stock_stock_value]),
CALCULATE(SUM(Query1[stock_stock_value]),ALLSELECTED(Query1)))

Problem:
There are three filters that I used date, product and distrirbutor filter but whenever I used the product filter the values changes to 100%. This only happens when I used the product filter and this only affects with this Measure other measures are not affected. I'm guessing its because of the "ALLSELECTED" 

Need your help


 

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Let me know if this measures work.

     

    Total SKU SUM DIST = SUM(Data14[TOTAL SKU Value])
     
    SUM SKU by Dist =
    CALCULATE(SUMX(Data13,Data13[SKU]),ALLEXCEPT(Data14,Data14[Distrubutor Name]))
     
    SKU Stock Contribution(%) = Divide([SUM SKU by Dist],[Total SKU SUM DIST],0)
     
    Data13 is ChartTable1 and Data14 is ChartTable2
    I added some more dummy values to validate my results and it seems to work fine.
     
     
    Regards,
    Harsh Nathani
     
     

10 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

     

    Try ALLSELECTED() without specifying a table.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Mariusz, I can see the 100% whenever I selected the product 

      • Mariusz's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Anonymous 

         

        What would you expect to see, if you expecting to see the percentage of total regardless of the filter context then you can use ALL() instead.

         

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        LinkedIn

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago
      Genius!
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Could be. Could be a missing relationship. Could be a few other things. Would need to see how your data is laid out, is everything in a single table? Can you provide sample data to reproduce the problem?

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    You might consider replacing ALLSELECTED with an ALLEXCEPT or REMOVEFILTERS. Not certain what filters you want to keep and what you want to remove for your "ALL" part of your calculation.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Greg_Deckler,

      I'm not really sure with the "ALL SELECTED" but I cannot get the percentage of sku sum/total sku sum without it. If I remove it the result is 100% all data.

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous  - Sample data posted as text to recreate would really help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

         

        It sounds like you have a SKU. And SKU's are grouped into Groups. You have some other dimensions. You want to calculate a particular SKU's impact on a Group keeping your other dimension selections such as date, etc.

         

        That is a fairly sophisticated calculation and depends greatly on exactly how your data is laid out.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Let me know if this measures work.

     

    Total SKU SUM DIST = SUM(Data14[TOTAL SKU Value])
     
    SUM SKU by Dist =
    CALCULATE(SUMX(Data13,Data13[SKU]),ALLEXCEPT(Data14,Data14[Distrubutor Name]))
     
    SKU Stock Contribution(%) = Divide([SUM SKU by Dist],[Total SKU SUM DIST],0)
     
    Data13 is ChartTable1 and Data14 is ChartTable2
    I added some more dummy values to validate my results and it seems to work fine.
     
     
    Regards,
    Harsh Nathani