Forum Discussion

RafalK's avatar
RafalK
Advocate IV
10 years ago
Solved

Calculating share based on filtered column

Hi,

I am wondering if this is currenly possible in Power BI.

I have a table with columns: month, client, sales. I am adding a new column with total sale for month and then divide sale / total sale per month to get share.

What i want to do now is to make this dynamic so that i can choose several clients only and calculate the total sale for month only for those clients. I can do that by filtering clients in the source sql but this requires active connection to the database to change my calculation. 

 

Is there any way to do such filtering without a need of refreshing the data from the data source?

 

Thanks

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    Sounds like you want to use a Slicer visualization. You can select multiple items in a slicer. You can also use Page, Report and Visual level filters.

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    10 years ago

    RafalK When you have created new column and applied formula of sales / total sales per month it would have calculated that based on values in each row. Therefore on report designer if you drop in slicer and select only few clients you should get share values only for those clients as from your table values only from those rows would be returned. Hope this make sense :smileyhappy:

    • RafalK's avatar
      RafalK
      Advocate IV
      10 years ago

      Thanks for the answer but this will not fix my problem.

      If i use a slicer, i will get shares only for the clients i want but those shares will still be the same. I want the total and therfore the share value to change when i select just a few clients.

       

      Example:

      I have 3 clients: A with sale 100$, B with 50$ and C with 20$. Together that gives me 170$ total and a share of:

      A: 100/170 = 59%

      B = 50/170 = 30%

      C = 20/170 = 11%

       

      Now if i select only clients B and C i don't want to get 0,30% and 0,11% but:

      B = 50/(50+20) = 71%

      C = 20/(50+20) = 29%

       

      Right now this is all done using DAX, where i calculate Sales[Value] / SUM(Sales[Value])

      • SabineOussi's avatar
        SabineOussi
        Skilled Sharer
        10 years ago

        Hi Rafal,

         

        You need to create this calculated measure and not column:

         

        DIVIDE(SUM( Sales[Value] ) ,
        CALCULATE(
        SUM( Sales[Value] ),
        ALLSELECTED( Sales[Client] )
        ))

         

        Or Client[Name] instead of Sales[Client] depending on how your tables are contructed and related.

         

        Tell me how it goes.