Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
SDittmannFleet
Advocate IV
Advocate IV

Supplier vs. Customer Total in Matrix Visual for Power BI

I have a matrix with "Supplier" and "Customer" in the row fields, the latter being nested underneath "Supplier". I want to include a column, "Rejected Expense". If I simply do an aggregated field, it lists the "Supplier" total and repeats it for all of the customers underneath it.

 

I wrote a measure to change this, but it doesn't give me a correct "Supplier" total. Basically, the expected outcome is:

  • If there is a value for at least one customer row, return the sum of all customer rows as the supplier total.
  • If there are no customer row values, return blank.

However, that is not what is happening. It takes the LAST customer value and returns it as the supplier total:

SDittmannFleet_0-1687982048349.png

 

This is the measure as I have it to-date:

 

 

Rejected Expense = 
VAR Cust_total =
    CALCULATE (
        SUM ( Rejections[Grand_Total__c] ),
        MAXX ('Price Schedule', 'Price Schedule'[Customer Account ID] ) = 'Rejections'[Customer_Branch__c]
    )

VAR Supp_total =
    CALCULATE (
            SUM ( Rejections[Grand_Total__c] ),
            MAXX ('Price Schedule', 'Price Schedule'[Supplier Account ID] ) = 'Rejections'[Supplier_Account__c]
        )

RETURN
    IF (
        ISFILTERED( 'Price Schedule'[Supplier Account ID] ),
        Supp_total,  // if current row is supplier row, return supplier total
        IF (
            ISBLANK( Cust_total ),
            BLANK(),    // if no customer total, return blank
            Cust_total // else return customer total
        )
    )

 

 

This is the data model:

 

SDittmannFleet_1-1687982186262.png

I found a video online where he works with a virtual table and forces the totals to show up correctly here, but I couldn't even get it to produce said table in DAX Studio, so no luck there. I know totals/subtotals in a matrix can be finicky, but there HAS to be a way to do it.

 

Any pointers would be greatly appreciated!

1 REPLY 1
SDittmannFleet
Advocate IV
Advocate IV

Update: it appears that the measure picks the LARGEST value, but only if there are contiguous items underneath the supplier line?!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.