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
natalia88
Helper I
Helper I

Gauge chart - doubles data in total

Hi All

I need to create a KPI dashboard where I calculate two KPI's (power BI file and Excel attached):

1) % of "Done" workshops per division

2) % of "Done" reviews per division

 

I have two questions:

1) When I create the Gauge chart, it always just doubles the number of entries it found in the total, instead of counting the total number of entries. How do I fix that? Example: if I select only "Workshop" and status only "Done", it should show 8 records from 72 records. But it shows 8 records from 16 records.

2) How do I change it to percentage? If I cnahge to "percent of grand total", it always shows me 100%.

 

pic1.pngpic2.png

 

 

Files are here:

https://drive.google.com/drive/folders/1FzUurRAjTORWHFv-WM5pPx9pZqBzx8nm?usp=sharing

 

Thanks for you help!

Natalia

 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@natalia88 see attached hope it helps. you have to create a measure to assign the target and also added a card visual for %



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

9 REPLIES 9
kentyler
Solution Sage
Solution Sage

Heres the link https://drive.google.com/drive/folders/1Q-GXNEfHIurDCMVfJCDdGc2H-BYO67Wc?usp=sharing





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


kentyler
Solution Sage
Solution Sage

That one was fun.

I was able to get a donut chart to respond correctly, but not the guage
statuschart.png
To create the chart I had to write 3 measures:
Num Rows = countrows(all(Table13))
this gives me an overall row count, regardless of what the slicers have been set to.
Rows Selected = countrows(Table13)
this gives me a count of the number of rows selected by setting values in the 2 slicers
Percent selected = Divide([Rows Selected],[Num Rows]) * 100
this divides those 2 numbers to give me the percent selected
The issue here, i think, is that the 2 slicers filter EVERYTHING, unless you use measures to get things like the total count, because in the measures you can do things like use ALL(), which removes the filters put on by the slicers.
I cannot get the guage to respond in a reasonable way to these numbers, even when they are correct, but a donut chart works just fine.
I will load your sample back to google drive, with my initials added to the name.
 




Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


parry2k
Super User
Super User

@natalia88 see attached hope it helps. you have to create a measure to assign the target and also added a card visual for %



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k 

I was too qiuck! It's working fine, but the problem now is that if I click on one Division, I want the target to also show per Division. I.e. for Div1, it should be 4 out of 18 instead of 4 out of total 72. And for two divisions, it should be "Nr of "Done" from 18x2, etc

Is it possible?

@natalia88 change you target measure to

 

Target = 
CALCULATE( 
COUNTROWS( Table13 ), 
ALLEXCEPT( Table13, Table13[Divusion] ) 
)

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k

thank you! And sorry if I'm asking easy questions, I just started in PB...

it works now, but if there're no entries (i.e. all "N/A"), then the target is "blank" - any chance it can be fixed?

(I have a filter on status, if it makes any difference)

 

pic1.png

 

@natalia88 what you want the target in that case? If you want target to be zero, add 0 to the target measure

 

Target = CALCULATE( COUNTROWS( Table13 ), ALLEXCEPT( Table13, Table13[Divusion] ) ) + 0


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k

hi Parry, 0 won't work because the target should be 36 if no division is selected and 9 if one division is selected (independent of whether it has anything "Done" or not, as it's the total number of workshops/reviews in each separate division).

Thanks a lot! Worked like a charm!

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.