create percentage on overall total with filter applied to visual

[smetdi]

Hi, 

I want to create a matrix, this is the case: 

I need to create a calculated measure to see the percentage of the missed column versus to overall total, 

this should be the result: 

Any advise on this? I'm new on Power BI and does not succeed in creating this measure. 

Thanks

[smetdi]

Pete,

Thanks for the reply, 

I'm trying to replicate this formula in my solution, no error, which is great, but result shows me 0

here is my formula: 

Net missed% vs Total = VAR __netdelivered =

calculate(sum('CSL + Root Causes'[Net actual units ordered TR]), ALL('CSL + Root Causes'))

RETURN

Divide([Net missed TR],__netdelivered,0)

Table = CSL + Root Causes

Net missed TR is a calculated measure based on this column

the variable __netdelivered does not calculate data, 

[BA_Pete · Accepted solution]

@smetdi 

Your formula looks fine.

What happens if you create a new measure with just this part of your code and put it in a card?

calculate(
  sum('CSL + Root Causes'[Net actual units ordered TR]),
  ALL('CSL + Root Causes')
)

If this outputs 0, then it sounds like a data issue, not a calculation issue. A possible cause could be that 'CSL + Root Causes'[Net actual units ordered TR] does not contain any data for some reason (maybe page filters or the incorrect column used etc.).

Another possible reason is that your [Net missed TR] measure is not calculating a value. What happens when you put this measure into a card?

It could also be that one or both of the above are producing very small values e.g. 0.0001 and, therefore, you are seeing very small percentage outputs that are being truncated by the default decimal places e.g. 0.004% appears as 0.00% to two decimal places.

Try the above then, if still no joy, I may need to have a look at your model/data if it doesn't contain any sensitive information. However, in my experience on here, it's probably a typo or simple human error.

Pete

[smetdi]

Hi Pete,

It will be the datamodel, it works fine when I only select fields from that table, but if I add some fields from other tables, then the data is wrong. I will have to check with the IT department for this

Thanks

Kind regards

[BA_Pete]

Hi @smetdi ,

Try the following measure:

_missed% = 
VAR __totalOrd =
CALCULATE(
    SUM(aTable[ordered]),
    ALL(aTable)
)
RETURN
DIVIDE(SUM(aTable[missed]), __totalOrd, 0)

This gives me the following output, unfiltered and filtered for zero missed:

Pete

[smetdi]

Pete,

Thanks for the reply, 

I'm trying to replicate this formula in my solution, no error, which is great, but result shows me 0

here is my formula: 

Net missed% vs Total = VAR __netdelivered =

calculate(sum('CSL + Root Causes'[Net actual units ordered TR]), ALL('CSL + Root Causes'))

RETURN

Divide([Net missed TR],__netdelivered,0)

Table = CSL + Root Causes

Net missed TR is a calculated measure based on this column

the variable __netdelivered does not calculate data, 

thanks