topic Re: Calculated Measure Not Working With Filter in DAX Commands and Tips

Calculated Measure Not Working With Filter

krodocker — Tue, 28 Jan 2020 20:38:05 GMT

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])))

Re: Calculated Measure Not Working With Filter

Stachu — Wed, 12 Feb 2020 10:35:01 GMT

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).

Column1	Column2
A	1
B	2.5

Re: Calculated Measure Not Working With Filter

Pragati11 — Fri, 14 Feb 2020 16:12:48 GMT

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.

Re: Calculated Measure Not Working With Filter

az38 — Sun, 16 Feb 2020 16:35:48 GMT

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