Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
saivina2920
Post Prodigy
Post Prodigy

How do we categorized from total values in pie chart.

I have below table and it is team wise category.

 

Let us consider Team "A" will have 4 rows. It is 100%

A = 5 Rows (100%)
B = Get count rows of Gender which will have blank values in Remarks column of team "A" = 2 Rows (40%)
C = Get count rows of Gender which will have Non blank values in Remarks column of team "A" = 3 Rows (60%)

How do we categorized from total values in pie chart..

 

1 ACCEPTED SOLUTION

Hi @saivina2920 ,

I see that it is not recognizing the BLANKs in the Code Column in your Power BI report.

You can replace those Blanks into Nulls and try to use that new column in getting your measures done. You can create measures by handling NULLs.

 

If you do not want to change the original column, add a new duplicate column as said above and create measure based on that. The Original Code Column can be used for display.

 

Also use a SELECTEDVALUE() DAX function to replace the hard coding of Type and to take the selected value in the slicer. Shown below the same

 

A% = 
Var C = CALCULATE(COUNT('Table'[Name]),'Table'[Team] = SELECTEDVALUE('Table'[Team]))
Var A = CALCULATE(COUNT('Table'[Name]),'Table'[Team] = SELECTEDVALUE('Table'[Team]) && ISBLANK('Table'[Remarks]))
RETURN
DIVIDE(A, C)

 

Regards,

View solution in original post

10 REPLIES 10
Thejeswar
Resident Rockstar
Resident Rockstar

Hi @saivina2920 ,

You can create 2 measures as below

 

A% = 
Var C = CALCULATE(COUNT('Table'[Name]),'Table'[Team] = "A")
Var A = CALCULATE(COUNT('Table'[Name]),'Table'[Team] = "A" && ISBLANK('Table'[Remarks]))
RETURN
DIVIDE(A, C)

 

 

 

B% = 
Var C = CALCULATE(COUNT('Table'[Name]),'Table'[Team] = "A")
Var B = CALCULATE(COUNT('Table'[Name]),'Table'[Team] = "A" && NOT(ISBLANK('Table'[Remarks])))
RETURN
DIVIDE(B, C)

 

Mark the measures as %

Use both the measures in a pie chart values section. The Pie Chart should look like the one below

 

Thejeswar_0-1650462608343.png

Regards,

 

Thanks for your reply.

I am using some alternative test data with adding more filter.  scenario is the same. But i am facing some issue in the DAX as mentioned below.

The important point is the chart should show based on the category. I have mentioned in the PBIX file. pls. refer.

 

1. B% = I am not able to add condition ISBLANK(TEST DATA(CODE)).

2. C% = I am not able to add condition NOT ISBLANK(TEST DATA(CODE)).

 

 

pls. find test PBIX file ==> https://1drv.ms/u/s!AiSRcgO5FUmN8WwCNNXjGUFATi1b?e=84HxbO 

 

Pls. Reply immediatly if possible.. This is very very urgent.

 

pls. let me know if you need any more details.

Hi @saivina2920 ,

You are unable to add both measure into the Pie chart because you are having Data in the Details section. If you remove the TYPE from the Details section, you should be able to add both the measures to the Pie chart

 

Thejeswar_0-1650531459232.png

For your reference

Thanks..I can able to add more than two measures.

But, the below two points i am not able to fix it.

1. B% = I am not able to add condition ISBLANK(TEST DATA(CODE)).

2. C% = I am not able to add condition NOT ISBLANK(TEST DATA(CODE)).

After Filtering,

B Result is : "2"

C Result is : "3"

like that the output will come...

Note : we should not add in the expression. because it is dynamic and it will come in the slicer.

Table'[Team] = "A"

 All the pie chart should come based on the Team. put, Team value in the Slicer.

Hi @saivina2920 ,

I see that it is not recognizing the BLANKs in the Code Column in your Power BI report.

You can replace those Blanks into Nulls and try to use that new column in getting your measures done. You can create measures by handling NULLs.

 

If you do not want to change the original column, add a new duplicate column as said above and create measure based on that. The Original Code Column can be used for display.

 

Also use a SELECTEDVALUE() DAX function to replace the hard coding of Type and to take the selected value in the slicer. Shown below the same

 

A% = 
Var C = CALCULATE(COUNT('Table'[Name]),'Table'[Team] = SELECTEDVALUE('Table'[Team]))
Var A = CALCULATE(COUNT('Table'[Name]),'Table'[Team] = SELECTEDVALUE('Table'[Team]) && ISBLANK('Table'[Remarks]))
RETURN
DIVIDE(A, C)

 

Regards,

Thanks for your guidance..

 

what is meant by "hard coding of Type"

saivina2920_0-1650601435214.png

 

 

amitchandak
Super User
Super User

@saivina2920 , not very clear. But you can create three measure and use them without a legend

Not required to create 3 measures.

We need to categorized 2 measures (B and C) from A (100%). Because "A" is the total which contains 100%.

Only B and C want to display in the pie chart after calculating the above said calculation.

How to create DAX for the above 2 measures...?

Anyone can help regarding the subject pls.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.