Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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).
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.
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |