Replace No Data into values in 100% Stacked

[Anonymous]

Hi,

I want to change no data in 100% Stacked chart into 100%. Example based on this picture

So my purpose for Europe itself must be 100% for Yes only. I create a measure and it makes the europe is 50% yes and 50% no.

Is there any way to achieve that?

Thanks,

Regards,
Connie

[v-lili6-msft · Accepted solution]

HI, @Anonymous 

You could try this way as below:

Step1:

Add a Yes/No fact table and then create the relationship with data table

Step2:

Use conditional measure like this:

Measure = IF(ISBLANK(CALCULATE(SUM('Table'[Value]),ALL('Type'[Type])))=FALSE(),CALCULATE(SUM('Table'[Value])),SUMX(FILTER('Type','Type'[Type]="Yes"),1))

Step3:

Drag Yes/No field from Yes/No fact table instead of data table

Result:

here is sample pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

Thank you for your replies, so which mean I need to create another table that contains only "Yes" and "No" with the ID (to connect to other tables) is it?

[v-lili6-msft]

hi, @Anonymous 

Yes, you need to create another table that contains only "Yes" and "No" like the sample pbix i have attached above.

Refer the pbix file above.

Best Regards,

Lin

[Anonymous]

Hi,

Checked already, just wondering since  I have 10 things like that which mean I will create 10 new tables for it.

Is it will make my power bi file become bigger? Or better I do that in the query instead of measure?

[v-lili6-msft]

hi, @Anonymous 

Just add a fact table with "Yes"/"No" , and since it just a fact table, it does not affect file size much.

Best Regards,

Lin

[Anonymous]

@v-lili6-msft Hi thank you for your reply,

I have same chart but 10 different legend, So it will be new 10 tables and I think will affecting my performance.

But indeed it's good answer if I only have 1 chart for it. But now I have 10 chart and 10 different legend, whic is I will create each legend into 1 table.

Thanks

[v-lili6-msft]

hi, @Anonymous 

You could create each legend into 1 table and it is same logic,

Just try it. 

Best Regards,

Lin