topic Re: Reference total of one measure in another in DAX Commands and Tips

Reference total of one measure in another

brewery_dba — Tue, 19 May 2020 23:16:01 GMT

Hi all,

I'm trying to get the total of one measure as the row value of another. This is to recreate Excel formulas from our finance dept.

In the example below, I need to 11,199,255 for all rows in TotalInvoiceAmt_PY. It works with my CY measure but not PY measures, probably because of the date filter I'm using. Can I get Power BI to refer to measure (not column) values?

TIA

InvoiceAmt_CY = sum('fact SalesInvoiceView'[SalesAmountTCY])

InvoiceAmt_PY = CALCULATE([InvoiceAmt_CY], SAMEPERIODLASTYEAR('dim DatePosted'[ActualDate]))

TotalInvoiceAmt_CY =

CALCULATE (

SUMX('fact SalesInvoiceView',[InvoiceAmt_CY]),

ALLSELECTED()

)

TotalInvoiceAmt_PY =

CALCULATE (

SUMX('fact SalesInvoiceView',[InvoiceAmt_PY]),

ALLSELECTED()

)

Re: Reference total of one measure in another

Anonymous — Wed, 20 May 2020 00:27:19 GMT

Your correct measures are:

TotalInvoiceAmt_CY = CALCULATE ( [InvoiceAmt_CY], ALLSELECTED() ) TotalInvoiceAmt_PY = CALCULATE ( [InvoiceAmt_PY], ALLSELECTED() )

Your measure returns BLANKs because... well, it's to do with the filter context and context transition. In a word, it's complex and it would take a lot of time and space to explain why you see what you see.

Advice is - you should never, ever do things like:

CALCULATE ( SUMX('fact SalesInvoiceView',[InvoiceAmt_PY]), ALLSELECTED() )

Summing over the entire (expanded) fact table with context transition because of a measure is a SURE WAY TO CALCULATE INCORECT NUMBERS. Please stay away from this practice. Please do not ever iterate over a fact table. This will not only be agonizingly slow. It's extremely dangerous for the correctness of the calculation.

Best

Re: Reference total of one measure in another

brewery_dba — Tue, 19 May 2020 23:59:14 GMT

Thanks for your feedback... could you recommend the correct approach to this problem? I have tried with ADDCOLUMNS ( VALUES but still get the row context.

Re: Reference total of one measure in another

Anonymous — Wed, 20 May 2020 00:22:01 GMT

Have I not given you the correct measures at the top of my post?

Best
D

Re: Reference total of one measure in another

brewery_dba — Wed, 20 May 2020 01:04:10 GMT

D, you certainly did. Much thanks!