Harvesting a user input and feeding back into measure which requires row context

[Lightice83]

I want to create a scenario analysis measure for Revenue which calculates at all levels of aggregation through the product hierarchy but currently I can only get it to calculate correctly at the lowest level of the product hierarchy. I also have a input parameter/Slicer for 'Order Quantity' but that is working correctly as it doesn't require row context

The basic details of my model are:

Product Hierarchy:              Product, Sub-Category, Category

Price:                                   Located in 'AW Product_Lookup' table

Order Quantity:                   Loctaed in 'AW 2015-2017 Sales' table

Price Adj Measure              Price Adjustment % Value

Order Adj Measure             Adj Order Quantity Value

I have created a Paramter/Slicer which a range of -1 to 1 & this input is harvested using the measure [Price Adjustment % Value]

My current standard revenue measure is the below & this works correctly: 

SUMX('AW_Sales_2015-2017','AW_Sales_2015-2017'[OrderQuantity]*RELATED(AW_Product_Lookup[ProductPrice]))

My scenario analysis measures are:

Adj Price =                 AW_Product_Lookup[ProductPrice] * (1+[Price Adjustment % Value])

Adj Order Quantity = [Total Order Quantity] * (1+'Adj Order Quantity'[Adj Order Quantity Value])

Adj Revenue =           [Adj Price] * [Adj Order Quantity]

The above works correctly for the Product hierarchy level but does not calculate correctly for Sub-Category or Category. My understanding is that this is because DAX Measures are effectively an Island & therefore this would be multiplying the Sub-Category Average price by the price adjustment value which is incorrect, I need to be multiplying the product level price by the ajdustment value

I recreated the standard revenue measure to feed in the parameter/slicer measure but if error'd & said it required row context. I also tried creating a new column within the Price table & feeding the parameter/slicer back into the table but this did not work either

Is there a way to create these scenario analysis that work correctly at all levels of the hierarchy?

Thanks!!

[TomMartens · Accepted solution]

Hey @Lightice83 ,

I created a measure tom revenue that looks like this:

tom revenue = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            'AW_Sales_2015-2017'
            , 'AW_Product_Lookup'[ProductKey]
            , 'AW_Product_Lookup'[ProductPrice]
        )
        , "@revenue" , 
            CALCULATE( SUM( 'AW_Sales_2015-2017'[OrderQuantity] ) ) * [ProductPrice]
    )
    , [@revenue]
)

The difference in comparison to your measure Revenue2 or the calculated column revenue is this

• I tend to avoid creating numeric calculated columns because they will consume precious memory whether they are used in data visualization or not. The compression of calculated columns is not as strong as the compression of base columns. Depending on the nature of the algorithm and the data distribution, there might be a huge number of distinct values. This makes things even worse: a large number of uncompressed distinct values.
• In contrast to the measure Revenue2 my measure only iterates across the summarized products to perform the multiplication with the price.
  If the number of rows inside your fact table is growing, it is a good strategy to reduce the number of rows before the application of expressions happen.

Here is the 2nd measure tom revenue adj

tom revenue adj = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            'AW_Sales_2015-2017'
            , 'AW_Product_Lookup'[ProductKey]
            , 'AW_Product_Lookup'[ProductPrice]
        )
        , "@revenueAdj" , 
            CALCULATE( SUM( 'AW_Sales_2015-2017'[OrderQuantity] ) ) 
            * ( 1 + [Adj Order Quantity Value] )
            * [ProductPrice]
            * ( 1 + [Price Adjustment % Value] )
    )
    , [@revenueAdj]
)

As you can see, it's an adoption of the first measure. In the above measure, you see the push down of calculations to the most granular level, the product I mentioned in one of my previous replies.

You will see also that I tend to avoid reusing measures, as this might "introduce" nested loops.

Most of the time it's a trade-off between performance and maintenance. The larger the dataset will become the more I avoid reusing measures.

Hopefully, this provides what you are looking for.

Regards,

Tom

[Lightice83]

Also how do you post a block of code into a reply like you did with the grey box? I tried using the 'Source Code' formatting option but it didn't work 😂

[TomMartens]

Hey @Lightice83 ,

to enter DAX or M snippets I use:

I use JavaScript as language for DAX and M as sometimes HTML recognizes something that is not there 😉

Regards,

Tom

[Lightice83]

Ahh cool - thanks @TomMartens 

[Lightice83]

Thanks so much @TomMartens This solution works perfectly & I really appreciate the time & effort you took to answer my question!

Can I ask about when you say that my 'revenue2' measure was a calculated column? I'm new to PBI but my understanding was that this is not a calculated column but a measure that does not live in the underlying data tables?

Revenue2 = SUMX(

'AW_Sales_2015-2017','AW_Sales_2015-2017'[OrderQuantity] * RELATED(AW_Product_Lookup[ProductPrice]))    

[TomMartens]

Hey @Lightice83 ,

I said that revenue is a calculated column and Revenue2 is a measure:

Regards,
Tom

[Lightice83]

Very true @TomMartens I misread the original post 

[TomMartens]

Hey @Lightice83 ,

you have to push down the adjustment to the row level calculation of the product.

But maybe you want to consider using 

measure = 
ADDCOLUMNS(
    SUMMARIZE(
        'AW_Sales_2015-2017'
        , AW_Product_Lookup[Product]
    )
    , "Order Quantity" , ...
)

Hopefully, this provides an idea on how to tackle your challenge.

If not, create a pbix file that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive, google drive, or dropbox and share the link. If you are using Excel to create the sample data, instead of the manual input method share the Excel as well.

Regards,

Tom

[Lightice83]

Thanks @TomMartens but can I reference a Measure in a 'ADDCOLUMNS' function?

I tried but it error'd saying 'Column 'Price Adjustment % Value' cannot be found or may not be used in this expression

[TomMartens]

Hey @Lightice83 ,

using ADDCOLUMNS/SUMMARIZE can reference measures. Of course it's important to note that ADDCOLUMNS and/or SUMMARIZE return tables, a measure is expected to return a scalar value.

In my above example I just sketched the ADDCOLUMNS / SUMMARIZE approach to "compose" a virtual table.

A final statement might look like the one below (also just a draft), please be aware that multiplication with the column etlloadid does not make any sense it's just for making the example more complex:

Measure 3 = 
var t = 
ADDCOLUMNS(
    SUMMARIZE(
        'FactOnlineSales'
        , 'DimProduct'[ProductKey]
        , 'DimProduct'[UnitPrice]
        , 'dimproductsubcategory'[etlloadid]
    )
    , "Online SalesAmount" , [SalesAmount online (SUM)] * [UnitPrice]
)
return
SUMX(
    t
    ,[Online SalesAmount] * [ETLLoadID]
)

Hopefully, this provides what you are looking for to tackle your challenge. If not, consider creating a pbix file that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix file to onedrive, google drive, or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, share the Excel file as well.

Regards,

Tom

[Lightice83]

Thanks @TomMartens . I get now that ADDCOLUMNS returns a table but I couldn't quite get my scenario revenue measure to work. Below is my code:

Adj revenue =

var t =

ADDCOLUMNS(AW_Product_Lookup,"Scenario Price", 1+[Price Adjustment % Value])

return

SUMX('AW_Sales_2015-2017', 'AW_Sales_2015-2017'[OrderQuantity]*RELATED(t[Scenario Price]))

The error was "The column t[Scenario Price] either doesn't exist or doesn't have a relationship to any table in the current content"

Any idea why I couldn't reference the new table stored in a variable in my related function?

Thanks

[TomMartens]

Hey @Lightice83 ,

here you will find details about RELATED: https://dax.guide/related/

Provide a pbix file that contains sample data, but still reflects your data model (tables, relationships, calculated column, and measures). Upload the pbix to onedrive, google drive, or dropbox, and share the link. If you are using Excel to create the sample file instead of the manual input method, share the xlsx as well.

Otherwise, I will not be able to spend more time on this, as it will become ping pong.

Next, consider Iterating over the filtered products using SUMX in combination with a base measue that SUMS the quantity, instead of the filtered facts.

Regards,

Tom

[Lightice83]

ok cool thanks @TomMartens 

I have used RELATED before for my standard revenue measure but I couldn't get it to work with my new ADDCOLUMN virutal table

Below is a link to the pbix file:

https://drive.google.com/file/d/1shhcaCdS9ijteBBAtUz0RhfwMYXoC--k/view?usp=sharing

The basic setup is:

Adj Revenue measure is located in 'AW_Sales_2015-2017'

I want to create a virtual table adding a new column to the 'AW_Product_Lookup' table using the 'Price Adjustment %[Price Adjustment % Value]' measure 

Then I want to create a ADJ Revenue measure using SUMX to iterate through the 'AW_Sales_2015-2017[Order Quantity] * The new column created in the virtual table

Thanks for taking the time to have a look!