cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
natalia88 Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Super User IV
Super User IV

Re: Gauge chart - doubles data in total

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






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

9 REPLIES 9
Super User IV
Super User IV

Re: Gauge chart - doubles data in total

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






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Super User II
Super User II

Re: Gauge chart - doubles data in total

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!




Super User II
Super User II

Re: Gauge chart - doubles data in total

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!




natalia88 Frequent Visitor
Frequent Visitor

Re: Gauge chart - doubles data in total

Thanks a lot! Worked like a charm!

natalia88 Frequent Visitor
Frequent Visitor

Re: Gauge chart - doubles data in total

@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?

Super User IV
Super User IV

Re: Gauge chart - doubles data in total

@natalia88 change you target measure to

 

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

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





natalia88 Frequent Visitor
Frequent Visitor

Re: Gauge chart - doubles data in total

@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

 

Super User IV
Super User IV

Re: Gauge chart - doubles data in total

@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





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





natalia88 Frequent Visitor
Frequent Visitor

Re: Gauge chart - doubles data in total

@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).

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors