Forum Discussion

ConnieMaldonado's avatar
ConnieMaldonado
Icon for Responsive Resident rankResponsive Resident
3 years ago
Solved

Use a Matrix to Display Values - Divisor is Fixed

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    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]) ))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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's avatar
    ConnieMaldonado
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

    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!

  • ConnieMaldonado's avatar
    ConnieMaldonado
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

    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: