Percentage of parts in a matrix issue using DAX within Power BI

Question

Basically I'm trying to find the defect percentage per "Defect Code."

The machine number is in one table ([Knitting]) and the defect code/defect quantity are in another table ([Knitting Defects]). They are related by piece number and are being affected by a calendar table. Processed Qty is from the Knitting Defects table as depicted below.

Problem is when applying the percentage calculation below, it works perfectly for the machine, but the percentage of the parts (defect codes) come out as 100%.

I have three DAX measures that I'm using currently.

 Defect Quantity = 
    CALCULATE(
        SUMX( 'Knit Defects','Knit Defects'[Processed Qty]),
        left('Knit Defects'[Defect Code])="5"
    )


Defect and 1st Quality = 
   calculate(
       sum('Knit Defects'[Processed Qty]),
           'Knit Defects'[Defect Code] = ""
       ) + [Defect Quantity]


Defect Percentage = 
  iferror([Defect Quantity] / [Defect and 1st Quality] ,0)

Are you able to provide any sample data and how your relationships are configured? — Joe Gravelyn
Here is a stripped .pbix for you too look at if you don't mind. Pulling my hair out over here. drive.google.com/file/d/1mQLTaKBImOx_vRWstri01Em65-dYVMR_/… — C.Mayers

RADO RADO · Accepted Answer · 2019-04-08T19:33:35

The problem is in your [Defect and 1st Quality] measure. If you drop it into the matrix, you will see that it's always the same as [Defect Quantity] for each Defect description:

It's only possible if the first part of your [Defect and 1st Quality] measure:

Defect and 1st Quality = 
   calculate( sum('Knit Defects'[Processed Qty]),
           'Knit Defects'[Defect Code] = "")

always returns 0 or blank.

Why is this happening? Because in your visual filters, you have selected only Defect Codes that start with "5". So, in this measure you are essentially asking Power BI to calculate sum of Processed Qty for the Defect codes that start from "5" AND equal "". Since such combinations don't exist, the formula returns blank.

To fix it, you need to "remove" the impact of the visual level filters first, and then apply a new filter:

Defect and 1st Quality =
CALCULATE (
    SUM ( 'Knit Defects'[Processed Qty] ),
    ALL ( 'Knit Defects'[Defect Description] ),
    'Knit Defects'[Defect Code] = ""
) + [Defect Quantity]

Result:

A couple of additional suggestions:

You can re-write Defect Percentage measure as follows:

Defect Percentage = DIVIDE([Defect Quantity], [Defect and 1st Quality])

It does the same thing, but much more efficiently, and the code is cleaner.

Also, I'd recommend to revisit your data model design. It needs to be converted into a proper start schema - as it stands now, it has a lot of dimensional modeling issues. If you don't fix them, DAX will keep being challenging for you.

Percentage of parts in a matrix issue using DAX within Power BI

1 Answers