Forum Discussion

tyan's avatar
tyan
Helper II
4 years ago
Solved

Pie Chart and switch function

Hi thank you for helping me. I new to BI and try to create some easy graphic to see the whole picture. 

 

So I would like to create a pie chart that show how many stores that their rating are less than 20%，20%~25%, 27%~33%, 34%~40% in a perctange way. 

 

this is the raw database table,. 

 

I tried to make the legend as Full score 100%.   <20%, 20%~25%， 25%~50%， 50%~70%  etc..

Once I uploaed my Rating% into Powerbi, it changes the format into Text. Do i need to change the format into number? if yes how to do it? 

 

My thought:

I used  swicth(true(),  rating%<20%, "<20%", rating%<20%,>=25%, "20%~25%",rating%<25%,>=50%, "25%~50%"))
It doesnt work.. somewhow rating with my rating% format. Cause i change power query, it displays 0.2   0.5 0.7 instead of %. 

 

 

This is the pie chart i would like to see, but legend different. 

 

Need Help
Show and Tell
  • VahidDM's avatar
    VahidDM
    4 years ago

    HI tyan 

     

    Use this code to add a new calculated column to your table:

    Cat. = 
SWITCH (
    TRUE (),
    [Rating] < 0.2, "<20%",
    [Rating] > 0.2
        && [Rating] <= 0.25, "20%~25%",
    [Rating] > 0.25
        && [Rating] <= 0.5, "25%~50%",
    [Rating] > 0.5
        && [Rating] <= 0.75, "50%~75%",
    [Rating] > 0.75
        && [Rating] <= 1, "75%~100%"
)

     

    Then use that to create a pie chart:

     

     

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

     

2 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    HI tyan 

     

    Use this code to add a new calculated column to your table:

    Cat. = 
SWITCH (
    TRUE (),
    [Rating] < 0.2, "<20%",
    [Rating] > 0.2
        && [Rating] <= 0.25, "20%~25%",
    [Rating] > 0.25
        && [Rating] <= 0.5, "25%~50%",
    [Rating] > 0.5
        && [Rating] <= 0.75, "50%~75%",
    [Rating] > 0.75
        && [Rating] <= 1, "75%~100%"
)

     

    Then use that to create a pie chart:

     

     

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

     

  • rbriga's avatar
    rbriga
    Impactful Individual
    4 years ago

    As a note on VahidDM 's solution, 

    The way SWITCH() resolves the data, you can do away with the first part of the && condition- SWITCH() already confirmed it on the last calculation step:

    =SWITCH (
    TRUE (),
    	Table1[Rank] < 0.2, "<20%",
   	Table1[Rank] <= 0.25, "20%~25%",
  	 Table1[Rank] <= 0.5, "25%~50%",
	Table1[Rank] <= 0.75, "50%~75%",
	 Table1[Rank] <= 1, "75%~100%"
)

    You may also wish to add a first clause to deal with null values- if you have any.