cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
harshadrokade
Helper IV
Helper IV

Showing % of cluster & not the entire data set in clustered column chart

HI,

 

I have below shown clustered chart created where I want to show % instead of numbers. If I use 'Show value as %' option available in Value field (right click), it calculates the % of the entire data set. But I want to calculate the % between each light blue & dark blue seperately. Example, I want to see in first light blue & dark blue % as 40% & 60% & not 2 &3.

 

How can I do this. Pls help

 

harshadrokade_0-1634936433552.png

 

Using 'Show value as %' option available in Value field (right click), gives result as below which I don't want. It is considering entire dataset as 100% then deviding all the bars in 100%. I want to consider each light blue & dark blue set of bars seperately as 100%

 

harshadrokade_1-1634936715442.png

 

2 ACCEPTED SOLUTIONS
jppv20
Solution Specialist
Solution Specialist

Hi @harshadrokade ,

 

Create this measure:

Percentage =
var TotalClass = CALCULATE(SUM('Table'[Count]),ALLEXCEPT('Table','Table'[Class]))
var Total = SUM('Table'[Count])
Return
DIVIDE(Total,TotalClass,BLANK())
 
Put it in table like this:
jppv20_0-1635150861725.png

 

If I answered your question, please mark it as a solution to help other members find it more quickly.

View solution in original post

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
harshadrokade
Helper IV
Helper IV

HI @jppv20 , Would it be possible for you to help me with above query?

jppv20
Solution Specialist
Solution Specialist

Hi @harshadrokade ,

 

Can you give some example data from your table? Doesn't need to be with real numbers.

Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur Sorry Sir, My organisation doesn't allow uplaoding the files anywhere & so I can;t share the PBIx file or link for the same. Will you be able to help me without PBIx file sir, pls

Hi,

Please prepeare a dummy dataset and show the problem clearly on that dataset.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

HI @jppv20 @Ashish_Mathur 

 

Pls refer below data. I have drawn the clustered column chart also as shown below which is giving wrong %. I want % to be shown on cluster as shown in the table.

 

Table name - Class

Columns as below-

'Class'Status'Count'% to be shown on Clustered column chart
Class APass2053%
Class AFail1847%
Class BPass2779%
Class BFail721%
Class CPass2757%
Class CFail2043%
Class DPass1131%
Class DFail2569%
Class EPass1335%
Class EFail2465%
Class FPass2651%
Class FFail2549%

 

Power bi visual with count-

harshadrokade_0-1635149822683.png

 

Power bi visual with % (% appearing here is wrong. I want the % as shown in table)-

harshadrokade_1-1635149906839.png

 

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

This is awesome. Thanks @Ashish_Mathur  Sir

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jppv20
Solution Specialist
Solution Specialist

Hi @harshadrokade ,

 

Create this measure:

Percentage =
var TotalClass = CALCULATE(SUM('Table'[Count]),ALLEXCEPT('Table','Table'[Class]))
var Total = SUM('Table'[Count])
Return
DIVIDE(Total,TotalClass,BLANK())
 
Put it in table like this:
jppv20_0-1635150861725.png

 

If I answered your question, please mark it as a solution to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.