Changing the measure per line in an expanded matrix

DAX DAX Calculations
1

So I have a matrix visual that I’m trying to have display different values for each line.
Here is a slimmed down example.

Machine                         Calculation
1                                       *Sum of all defects*
  Style                              *Sum of defects for Machine 1*
    Defect description    *Sum of defects as a part of Machine 1*

I’ve tried adapting an example that was used in “Getting Totals Correct When Using Advanced Logic - Power BI DAX” but since it’s a subtotal and not a grand total, the HASONEFILTER() wouldn’t be applicable. I’ve figured it out for when you are applying a measure to a heirarchy change but the application I need it for is when the matrix is expanded.

Defect Percentage Calculation:

Capture
Capture1387×441 94.4 KB

EDIT
Here is a link to an example of what I have currently in BI.

I’m not sure whether or not it’s even possible but basically I’m looking to have a different measure rather than the subtotal. I’m looking to get the machines to
show the defect total as:
(defects of machine) / (total defects);

where as the style and defect description needs to be:
(defects of style or defect description) / (defect total for machine)

instead of a subtotal if that makes sense? I’ve tried everything but I’m not sure if matrices are built that way?

2

I’m probably going to have to see a demo model here.

It looks like your just using a table, rather than a matrix? I’m not sure by what you’ve added.

Also you usually don’t look to place these in rows to work these out. Working across column is better.

By reading the above I don’t really know where to start to help though unfortunately.

There’s a bit going on here in the scenario, with the different context and also hierarchies you have, so guessing a formula wouldn’t really work here.

If you can setup some demo example then I’m sure the ultimate answer won’t be too difficult.

Thanks
Sam

3

I’ve edited the post to include a bit more information.

4

The key to these is to really simplify it and see if you can create what you need one by one.

Sometimes when looking at a bigger problem like this, I always look to bring it down to it’s simpliest form then build it back up again.

I just bought it done to one dimension in the matrix then tested the above formula.

image
image.png1372×685 56.5 KB

So in theory here you should be able to place any measure in for the total.

Then you could do the same again for other dimension to and build it out like I mentioned.

image
image.png1557×701 64.1 KB

So again you can find a way to know in a formula if you are in a particular subtotal by using logic like I have here.

See how you go with this.

Is Subtotal = 
IF( ISFILTERED( 'Knit Defects'[Defect Description]  ), BLANK(), TRUE )


Is Subtotal 2 = 
IF( AND( ISFILTERED( 'Knit Defects'[Department] ), NOT( ISFILTERED( 'Knit Defects'[Defect Description] ) ) ), FALSE, TRUE )

Sam

5

I literally tried everything except ISFILTERED(). Such a simple approach. Thanks for the help and I love the videos!

6

Ok that’s great. Good luck

Sam