Can not show all items when items not belong to a selection

[Anonymous]

Hi everyone, 

I want to calculate how much each market spend on its campaign.

Logic:

If Category = "Other", cost of this campaign with be distributed to all Category in "distribute" table by % recpectively.

My measure:

SEM Cost before distribute = sum(cost[cost])+0

SEM Cost =

var other = CALCULATE([SEM Cost before distribute] , filter(all(category), category[category] = "other"))

var _percent =SELECTEDVALUE(distribute[%])

return

IF(SELECTEDVALUE(category[category]) = "other" , other*_percent, [SEM Cost before distribute] + other*_percent)

The model:

The records of each table:

What the dashboard shows:

What I expect:

- When select market "fb":

Category	SEM Cost
hats	0,00
jeans	1.4
other	0.2
shirts	0.4

- When select market "bing"

Category	SEM Cost
hats	0,00
jeans	7.7
other	0.1
shirts	0.2

Hope to hear from you guys soon. Thank you so much

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

The relationships in your model caused the error.

Try to change like this.

Then results are what you want.

You can download my attachment for details.

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-stephen-msft 

Thank you so much for your help

In case it is not allowed to change the model, is there any ways to resolve it? Thank you

[v-stephen-msft]

Hi @Anonymous ,

Since you can't change the model, it's hard to achieve.

Due to the limitations of the relationships, your % is not passed correctly.

Is it okay to create a new table through a calculated table or add a new table to the data source?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.