Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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:
However, that is not what is happening. It takes the LAST customer value and returns it as the supplier total:
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:
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!
Update: it appears that the measure picks the LARGEST value, but only if there are contiguous items underneath the supplier line?!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
124 | |
79 | |
50 | |
38 | |
38 |
User | Count |
---|---|
195 | |
80 | |
70 | |
51 | |
42 |