Totaling Issues with multiple layers of detail

[afranci14]

Hello!

I am having a problem with a DAX calc (fairly simply done) that takes two other calcualted values (From other measures) and multiples them. In particular, it takes a difference in price between two periods and multiplies that difference by the total units sold in the second of the two periods. This gives us a great pricing impact when looking at two seperate periods or how we are currently performing. The issue at hand is that the measure is not totaling correct and the usual SUMX solutions aren't working due to the data being broken down by Customer info, then contract and GPO info, then Item info as opposed to just one layer to subtotal. 

My Current DAX for this measure is super simple, [Comp Item $ - Item $]*[Total Units] = Total Price Difference. Those are both seperate measures within themselves as well. 

Couple notes!

1) I am not trying to create any calculated columns just yet, our data model is very large as it is.

2) I can post some dumby data to show the issue, but can't post the whole table unfortunately. 

3) The way we compare two periods is using a comparison calender table within our model. this means when writing DAX, if you want a measure to use the comparison period as opposed to our current period, you have to use the comparison calender date hierarchy. For this dashboard, we are comparing a 1/1/21 through 4/3/21 to Feb of 22.

let me know your thoughts and I look forward to seeing if my first post gets any traction!

[amitchandak · Accepted solution]

@afranci14 , If you are doing a multiplication then you need to force calculation at some group say a product

Total Price Difference =

sumx(Values(Table[Product]), [Comp Item $ - Item $]*[Total Units] )

if you need to compare two period using slicer then refer

How to use two Date/Period slicers
https://youtu.be/WSeZr_-MiTg

[afranci14]

love the response, it helped a bit! So I am much closer (with 5% of actual amount) when looking at what PBI says is the total and then exporting the dash into Excel to see what the total is. I think my issues are around the fact that if I give context to Item attricbutres (number or name) then its still lacking context for the other levels of detail.

FYI, the fields I have on the dash are:

Division / Cust # / Cust Name / GPO Desc / Contract Code / Contract Code Desc / Platform / PRP2 / Item # / Item desc / Base Qty / Base Margin Sales / Base Item price / Comparison Qty / Comparison Margin Sales / Comparison Item price / Total Price Difference / % Difference

I am currently giving context to the Item desc (a fully unique value in the data set) here: 

Total Price Difference =

SUMX(VALUES('Item'[Item]),

([Comparison Avg Unit Price] - 'Key Measures - Sales'[Item Price - Margin/Qty]) * [Comparison Total Units])

One thing I'm noticing is that if I change Item Desc to item Number or even try to give context to the customer level of detail, the total changes but the line items all still stay the same. I'm really at a loss for why this is happening. I thought it was rounding errors at first within our model or when being pulled from our other DB, but i reconciled our E1 DB with what we have in PBI and that doesn't seem to be the cause either. Sorry again, but any thought? I can post some dumby data if that helps at all.