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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adamnimmo
Helper I
Helper I

Measure subtotal incorrect for one column

Good dal all, I seem to be stuck on an issue and my regular workarounds are not working. I am attempting to create a matrix table which displays a range of data <= -1, each column is a measure and all but one are working correctly.

 

For my data the [Cost $ Delta] column is multiplying the [Inv # Delta] by [Cost] for each row, these are calculating correctly until the Totals line. I somewhat understand why this is happening and I can eliminate the incorrect subtotal with a HASONEFILTER formula, yet I can't seem to figure out how to get the correct sum (roughly $1.49mm).

 

NonFiltered

 

Filtered

 

Here is a rough breakdown of formulas for each column.

 

[Inv # Delta] - Sum 'Table1'[SoldQty] - Sum'Table2'[EstQty] (Filtering <= -1)

[Inv $ Delta] - Sum 'Table1'[Sold$] - Sum'Table2'[Est$]

[Cost $ Delta] - Sum 'Table1'[SoldQty] - Sum'Table2'[EstQty] (Filtering <= -1) * 'Table3'[Cost]

[Inv + PD # Delta] - Sum 'Table1'[SoldQty] + Sum'Table1'[PendQty] - Sum'Table2'[EstQty]

[Inv + PD $ Delta] - Sum 'Table1'[Sold$] + Sum'Table1'[Pend$] - Sum'Table2'[Est$]


Thank you all for your help, it is greatly appreciated.

1 REPLY 1
v-yuta-msft
Community Support
Community Support

Hi adamnimmo,

 

What's your expect result? Are you trying to calculate the difference of sum between two tables? If so, is there any relationship between table2 and table3? The result of  Sum'Table2'[EstQty] (Filtering <= -1) * 'Table3'[Cost] depends on if there is any relationship between table2 and table3. If there is a relationship, the result is based on the the basic columns in table2 and table3. Modify you measure using DAX like pattern below and try again: 

[Cost $ Delta] =
SUM ( Table1[SoldQty] )
    - CALCULATE (
        SUMX ( Table2, Table2[EstQty] * RELATED ( Table3[Cost] ) ),
        FILTER ( Table2, Table2[EstQty] <= -1 )
    )

 

Regards,

Jimmy Tao

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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