Forum Discussion

krodocker's avatar
krodocker
Regular Visitor
6 years ago

Calculated Measure Not Working With Filter

I am trying to calculate the percentage of "Spend" received for each "Item" per "Supplier" using the measure below. (For example, Item A has 60% Spend with Supplier1 and 40% Spend with Supplier2.) This measure calculates correctly until I apply a filter for Item... then all percentages show as 100%. How can I maintain the percentage when applying the filter?

 

Percentage of Spend Received by Supplier Per Item =

DIVIDE(
CALCULATE(SUM(TABLE1[Spend]),

FILTER(TABLE1,TABLE1[Supplier] = TABLE1[Supplier])),

CALCULATE(SUM(TABLE1[Spend]),
ALLSELECTED(TABLE1), TABLE1[Item] IN VALUES(TABLE1[Item])))

3 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    6 years ago

    Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

    Column1Column2
    A1
    B2.5

     

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    Hi krodocker ,

     

    Try modifying yourcalculation to:

    DIVIDE(
               CALCULATE(SUM(TABLE1[Spend]), FILTER(ALLSELECTED(TABLE1),TABLE1[Supplier] = TABLE1[Supplier])),

               CALCULATE(SUM(TABLE1[Spend]), ALLSELECTED(TABLE1), TABLE1[Item] IN VALUES(TABLE1[Item]))

               )

     

    Let me know if this works.

     

    Thanks.

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi krodocker 

    TABLE1[Supplier] = TABLE1[Supplier]

    it is a very strange condition for any filter, doesn't it? it will return you all the table