Use a Matrix to Display Values - Divisor is Fixed

[ConnieMaldonado]

I am using a matrix to display values for a report that contains sales and quantity data based on Type, Category, Subtype, Line Item.  Here is a link to a pbix containing mock data and my attempt at the report.

 https://drive.google.com/file/d/1Kj8r2ay6kTylyc8909VhaFuNYNeD0n3w/view?usp=sharing

The pbix includes the expected results on the 2nd tab.

The matrix displays the "Sales" and Qty" data beautifully, but I'm having trouble calculating Qty/Item so that it shows correctly.

The issue is that the divisor (sum of quantity) is based on Type, Category and Line item, and is fixed for each section.

As an example:

For the first line item (Buy a dishwasher), the Qty/Item = 8/150.  Same for all other line items for Type=Household and Subtype=Premium.  

The divisor is based on the line item = "Household" (Line Item = Type) and is sliced based on subtype.

These line items will not change - i.e., the data will always have a line item = Household to calculate the divisor.  There is another type not shown here (you can see it in the pbix) = remodel where the same calculation applies.

I can't figure out how to keep the value fixed and do the division properly.

I have other metrics to calculate, but if I can get this working, I can probably figure out the rest. 🙂

Thanks in advance for any help.

[amitchandak · Accepted solution]

@ConnieMaldonado , Try a measure like

calculate(Sum(Table[Qty]), filter(all(Table), Table[Item] = max(Table[Item Type]) ))

or

calculate(Sum(Table[Qty]), filter(all(Table [Item]), Table[Item] = max(Table[Item Type]) ))

[ConnieMaldonado]

Almost there!  Yay!

I was able to get Qty/Item to work for each of the Subtypes using:

Qty/Item =

DIVIDE(

    sum('Sales Data'[Qty]),

    CALCULATE(

        SUM('Sales Data'[Qty]),

        FILTER(

            ALL('Sales Data'),

            'Sales Data'[Line Item] = MAX('Sales Data'[Type])

            && 'Sales Data'[Subtype] = MAX('Sales Data'[Subtype])

        )

    )

)

However, for the total, I need to sum the quantities.  If I look at just the Divisor, I get the following: 

[ConnieMaldonado]

I'll give that a try tomorrow.  Thanks for the response!

I was also thinking - what if I create a table using the summarize function that creates the sums as I need them, and then link back to the table or do a lookup to get the divisor. What do you think about that (if I can't get the measure to work)?

Thanks!