Measure Calculations on Concatenated Columns

[ken_morrill]

Moving to a new, locked down data model where Table Columns cannot be added, nor Reference Tables created.  100% of our department metrics are based on a Concatenated Key 'Customers'[Customer Number] & 'Materials'[Product].  The concatenated result is not in the Fact table.

How can the following be created - for each Customer/Product instance?
Revenue Adjustment =

   Sales (Current Filter Context) - ((Total Sales in Prior Calendar Year of Current Filter Context / Total Qty in Prior Calendar Year of Current Filter Context) x Total Quantity in Current Filter Context )

Example:

• Customer ABC purchased 10ea of Item 123 @ $10.00 each in the current year (which is in the current filter context)
• In the Prior Year, Customer ABC purcased 20 of Item 123 @ $8.00 each
• The Revenue Adjustment for ABC123 = $20. 

[ken_morrill]

First - uh - really?  That fast?  😢

I swapped in the real names, however, and am getting errors.  The redlines values are legitimate in the model.

[MAwwad]

To create the Revenue Adjustment measure for each Customer/Product instance based on the concatenated key, you can use the following DAX formula:

Revenue Adjustment = VAR PriorYearSales = CALCULATE ( SUM ( 'Sales'[Sales] ), DATEADD ( 'Date'[Date], -1, YEAR ), ALLSELECTED ( 'Customers', 'Materials' ), 'Customers'[Customer Number] & 'Materials'[Product] = SELECTEDVALUE ( 'Customers'[Customer Number] & 'Materials'[Product] ) ) VAR PriorYearQty = CALCULATE ( SUM ( 'Sales'[Quantity] ), DATEADD ( 'Date'[Date], -1, YEAR ), ALLSELECTED ( 'Customers', 'Materials' ), 'Customers'[Customer Number] & 'Materials'[Product] = SELECTEDVALUE ( 'Customers'[Customer Number] & 'Materials'[Product] ) ) RETURN SUM ( 'Sales'[Sales] ) - ( PriorYearSales / PriorYearQty * SUM ( 'Sales'[Quantity] ) )

This formula first calculates the total sales and quantity in the prior year for the selected Customer/Product instance based on the concatenated key. It then uses these values to calculate the Revenue Adjustment for the current filter context by subtracting the adjusted sales from the total sales. The formula uses the CALCULATE function with the ALLSELECTED modifier to preserve the Customer/Product filter context, and the DATEADD function to go back one year. The formula also uses the SELECTEDVALUE function to get the concatenated key for the current filter context.