Matrix Column total on Measure is not the total of the lines

[charlie_lugosi]

Created a measure to calculate differences between minimum values and current value of selected items.

WOD =

VAR WMINUSD=CALCULATE(MIN('WO_ISSUE'[BasePriceUSD]),ALLSELECTED('WO_ISSUE'[WO]))

VAR WMINLC=CALCULATE(MIN('WO_ISSUE'[BasePriceLC]),ALLSELECTED('WO_ISSUE'[WO]))

RETURN IF (MIN('CurrencyChoice'[Currency])="US$",SUM('WO_ISSUE'[BasePriceUSD])-WMINUSD,SUM('WO_ISSUE'[BasePriceLC])-WMINLC)

When I apply this into Matrix Columns the row level values are correctly showing the difference between current value and the minimum however the column total is not. It is the total of the calculated value (BasePriceUSD or BasePriceLC).

Can you please advise me , how to correct this.

P,S, I'd like to upload my whole PBIX but could not find out how.

[Fowmy · Accepted solution]

@charlie_lugosi 

I modified the measure, please try now:

WOD = 
SUMX(
    VALUES('WO_ISSUE'[Issued]),

        VAR WMINUSD=CALCULATE(MIN('WO_ISSUE'[BasePriceUSD]),ALLSELECTED('WO_ISSUE'[WO]))
        VAR WMINLC=CALCULATE(MIN('WO_ISSUE'[BasePriceLC]),ALLSELECTED('WO_ISSUE'[WO]))
        RETURN  
        IF (MIN('CurrencyChoice'[Currency])="US$",CALCULATE(SUM('WO_ISSUE'[BasePriceUSD]))-WMINUSD,CALCULATE(SUM('WO_ISSUE'[BasePriceLC]))-WMINLC)
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Fowmy]

@charlie_lugosi 

What do you expect at the total level?

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
________________________________________________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[charlie_lugosi]

Thanks for the instruction, below link for the PBIX file.

BOMCostTrends.pbix 

What I expected that the WOD column total is the total of the underlying rows.

[Fowmy · Accepted solution]

@charlie_lugosi 

I modified the measure, please try now:

WOD = 
SUMX(
    VALUES('WO_ISSUE'[Issued]),

        VAR WMINUSD=CALCULATE(MIN('WO_ISSUE'[BasePriceUSD]),ALLSELECTED('WO_ISSUE'[WO]))
        VAR WMINLC=CALCULATE(MIN('WO_ISSUE'[BasePriceLC]),ALLSELECTED('WO_ISSUE'[WO]))
        RETURN  
        IF (MIN('CurrencyChoice'[Currency])="US$",CALCULATE(SUM('WO_ISSUE'[BasePriceUSD]))-WMINUSD,CALCULATE(SUM('WO_ISSUE'[BasePriceLC]))-WMINLC)
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[charlie_lugosi]

Thanks very much, excellent.

Would you  please be kind giving a short explanation?

Thanks

[Fowmy · Accepted solution]

@charlie_lugosi 

I took your measure and used SUMX to iterate over WO_ISSUE'[Issued] field values and sum. at line level one value is visible and the measure shows the same value but at the total level, it goes through each value and aggregate. I used CALCULATE to set the context transition as well.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn