Referencing a SUMX measure in another measure

[Anonymous]

Hi everyone,

I have a sales table and a products table. In the sales table I have the the quantity of units sold broken down by product by date, and in the products table I have all the product pricing information.

To calculate the sales revenue I have created the measure:

GSV = SUMX('Sales',[Sales Units]*RELATED(Products[Unit SP]))

I have used and tweaked that same formula multiple times for things such as cost of goods.

However, I want to be able to able to add a slicer on a disconnected table for some basic 'what if' scenarios, e.g. what if sales units are only 50% of that etc. I therefore want my other measures to all be updated when I apply the filter which changes the number of sales units. So, when sales units are reduced, my revenue and COGs etc are updated accordingly. This won't happen if I just continue to use SUMX(....(RELATED( on every measure rather than using one measure within the others.

I'd therefore like to use the measure for [Sales Units] within other measures, but I am having trouble with this when using the related function.

If I create a measure called [Total Sales Units] with =SUMX('Sales',[Sales Units]) and then try to use this measure in another such as GSV with the formula: GSV= [Total Sales Units]*Related(Products[Unit SP]) I get the error 'this formula is invalid or incomplete: 'the column 'Products[Unit SP]' either doesn't exist or doesn't have a relationship to any table available in the current context'

I'm confused about why I can use SUMX with related if I explicitly use SUMX but not when I reference a measure created using SUMX.

Any help would be much appreciated!

Many thanks,

Paddy

[Anonymous · Accepted solution]

@Anonymous 

You should not do something like

GSV = SUMX( 'Sales', [Sales Units] * RELATED( Products[Unit SP] ) )

but should bring 'Unit SP' as a column to the fact table and do

GSV = SUMX('Sales', Sales[Sales Units] * Sales[Unit SP])

or, even better, you should store the multiplication in the fact table completely and write the measure as

GSV = SUM( 'Sales'[Amount] )

where Amount is  Sales[Sales Units] * Sales[Unit SP]. The above is not only simpler, it's potentially faster.

Secondly, RELATED needs a row context to work, which is why you get an error. Such a formula

GSV = [Total Sales Units] * Related( Products[Unit SP] )

does not expose any row context anywhere, whereas SUMX does: it creates a row context for its second argument.

Thirdly, you should always remember that measures must never be preceded with their hosting table's name and columns must always be preceded with their hosting table's name. The reason can be found in "The Definitive Guide to DAX" by The Italians.

Best

D

[mahoneypat]

Here are two potential approaches - 

1. NewMeasure = [OriginalGSVMeasure] * Selectedvalue(Slicer[Value])

2. In the SUMX of your original measure, created a variable for Selectedvalue(Slicer[Value]) and then use that as part of the iterator - 

 GSV = SUMX('Sales', var selected = selectedvalue(Slicer[Value]) return selected * [Sales Units]*RELATED(Products[Unit SP]))

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat