CODEWITHSUNDEEP
powerbidax
Fraita
Fraita

Reputation: 83

Summarized Value and Grand Total are wrong in PowerBI DAX

I'm working with claims where I use different key joins/sheets to get the whole truth. Right now I got two different sheets with costs for each item (Price Per Unit), depending on where the repair occur. One for Asia and one for the rest of the world (WW)

Sheet 1: PN-List Asia and Sheet 2: PN-List WW

Inside the mentioned sheets there is a priority (1 - 3) of which supplier I should get prices from. Column Prio.

I've made a column that either says Asia or WW for each job ID. Regress List [Continent].

Two problem has occurred, total cost for each ID is only the max value (most expensive item). See picture: First Problem

The total cost should be: 840,96 but it says 622,10 now.

The second problem is that my grand total is completely wrong, should be a lot more. See below picture:

Problem 2

The formula I've used:

    Price Per unit =
IF (
    SELECTEDVALUE ( 'Regress List'[Continent] ) = "Asia",
    CALCULATE (
        MAX ( 'PN-List Asia'[Price Per Unit] ),
        FILTER ( 'PN-List Asia', 'PN-List Asia'[Prio] = MIN ( 'PN-List Asia'[Prio] ) )
    ),
    CALCULATE (
        MAX ( 'PN-List WW'[Price Per Unit] ),
        FILTER ( 'PN-List WW', 'PN-List WW'[Prio] = MIN ( 'PN-List WW'[Prio] ) )
    )
)

Upvotes: 0

Views: 1008

Answers (1)

Fraita
Fraita

Reputation: 83

I just made a new measure:

Price Per Units = SUMX('Regress List', 'Regress List'[Qty] * [Price Per unit])

And it gave me the right answer

Thanks to Ozan Sen for leading me in the right way with Sumx

Upvotes: 1

Related Questions