Reference total of one measure in another

[brewery_dba]

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()

)

[Anonymous · Accepted solution]

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

D

[brewery_dba]

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. 

[Anonymous]

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

Best
D

[brewery_dba]

D, you certainly did. Much thanks!