Visualization in Gauge - Error in counting rows for percentage

[Anonymous]

Hi Guys, 

Could you please help me? I want to do something which seems simple but I don't understand the error.

I have 3 main status in my report called ZOE03:

• Fully Delivered
• Not Delivered
• Partially Delivered
• (I also have in the same column one callsed Not relevant for Deliverybut I dont need it so I filtered)

I want to show how much percent of one Sales Order was delivered. Want to do it via Gauge Visualization

So when the person click on one sales order it will appear X% Delivered, Y%Not delivered and Z% Partially Delivered

So Basically I thought it would be dividing total rows for Fully Delivered and divide by All the status in the colum. I used the formula below:

First I used 

Count Fully Delivered = CALCULATE(COUNTROWS(FILTER(ZOE03;ZOE03[Status]="Fully delivered")))

It worked out! So I tried to finalize with 

% Delivered  = CALCULATE([Count Fully D]/CALCULATE(COUNTROWS(ZOE03);ZOE03[Status];ALL(ZOE03;ZOE03[Status])))

Nothing worked and I still received the message below

MdxScript(Model) (4, 90) Calculation error in measure 'DeliveryPlan'[perc Measure]: Cannot convert value 'Not relev for deliv' of type Text to type True/False.

Any club on what to do or a better idea to make this visualization?

Thank you

[v-piga-msft]

Hi @Anonymous,

Have you solved your problem?

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

[NickolajJessen]

First off, i'd make a measure for the second part of your calculation.

and isn't it as simple as saying?:

total lines = CALCULATE(COUNT(Sales_Data[Product ID]);ALL(Sales_Data))

'' Example from my BI where it counts the total number of sales orders

[NickolajJessen]

Use the total calculation to fill in the maximum value

After that. fill in your filtered value:

If you're looking for at way to have multiple colours in your gauge. it's not possible (to my knowledge).

But you can be a bit clever about it and make it work with the area 'target value' 'Tool tips' and measueres. 
https://community.powerbi.com/t5/Desktop/Radial-Gauge-charts-with-two-value-fields/td-p/52727

[Anonymous]

Hi @NickolajJessen

Thanks a lot for the support!

I tried with sales data but did not work but after getting the logic of what you said I did on material level / delivered or not delivered and it worked!! 🙂

Just one last thing...would it be possible to have this value in %?

[NickolajJessen]

Maybe try a setup like this, where you use the '% of delivered' as primari value. Leave the maximum value out. (it will default to 100% this way). 

Then just fill in '% not delivered' And all else into the tooltip

[Anonymous]

Almost there. Actually I tranformed total of fully delivered (which is the calculation I did) into % then moved the Total Material lines to % as well but it is counting as everything and does not move when I change the Sales number.

You used any formula for that or just transformed in %?

[NickolajJessen]

Can you show me the formula for your '%GT count fully D'

[Anonymous]

Sure!

It is

Count Fully D = CALCULATE(COUNTROWS(FILTER(ZOE03;ZOE03[Status]="Fully delivered")))

Then I transformed it into %

[NickolajJessen · Accepted solution]

Ahh, that's where it goes wrong. Turn your count fully D into a normal number.

Create a new measure:
%delivered = Count fully D/Total M lines

This will give you the % you want

[Anonymous]

It worked out!!

Thank you Nick!! 🙂

[NickolajJessen]

Glad to hear it. 

Your are welcome