Get Sales based on common dimensions

DAX DAX Calculations
1

Hi all,

Please see attached basic pbix:
Sales for Brand and City.pbix (34.6 KB)

I have a simple data model containing two dim tables:

  • dimProducts
  • dimStores

And two fact tables

  • factStock
  • factSales

My requirement is to show a ‘Stock Report’ which is essentially outputs fields from factStock with the addition of City and Brand from dimStores and dimProduct.

I now need a measure called [Sales for Brand and City], which will return the [No of Sales] from factSales, where the Brand and City are the same as that shown on current row of the Stock report. See example below:

image
image968×592 29 KB

Many of the examples I have come across, refer to creating a measure like below:

Sales for Brand and City = 
CALCULATE([No of Sales], 
    dimStores[City] = MIN(dimStores[City]),
    dimProducts[Brand] = MIN(dimProducts[Brand])
)

However when I add this to my Stock Report table, the data explodes out.

Any suggestion appreciated!

Thanks

Mark

2

Hi all,

Hopefully my question hasn’t stumped all 80+ people who have kindly viewed my post :slight_smile:

To explain my issue again…

I need to iterate through my factStock table, returning [No of Sales], for that stock records related Brand and City. However Brand and City are not stamped on the stock record, I only have the ProductID and the StoreID. I therefore need to retrieve the current rows Brand and City (from their respective dim tables) and then retrieve the [No of Sales] for that given Brand and City from table factSales.

I have been working on a new measure [Sales for Brand and City 2 ] to do this, through the use of a temporary table __TempTable, which summarizes sales as required, by Brand and City. However I can’t figure out how to retrieve my value in the final LOOKUPVALUE?!

To prove my solution is close, I created a physical table called tableSalesByBrandAndCity and modified the measure to reference this and it works, however this is not dynamic.

Is someone able to modify this measure, so the final retrieval happens from __TempTable and not tableSalesByBrandAndCity, therefore keeping the measure dynamic?

Sales for Brand and City 2 = 
VAR __TempTable =
    CALCULATETABLE (
        SUMMARIZE (
            factSales,
            dimProducts[Brand],
            dimStores[City],
            "#Sales", [No of Sales]
        ),        
        ALLSELECTED ( dimProducts[Brand]),
        ALLSELECTED ( dimProducts[ProductID] )
    )

RETURN
    MINX( 
        factCurrentStock,
        VAR __SelectedProductID = min(factCurrentStock[ProductID])
        VAR __SelectedStoreID = MIN(factCurrentStock[StoreID])
        VAR __SelectedBrand = LOOKUPVALUE(dimProducts[Brand], dimProducts[ProductID],__SelectedProductID)
        VAR __SelectedCity = LOOKUPVALUE(dimStores[City], dimStores[StoreId], __SelectedStoreID)
        RETURN
        // LOOKUPVALUE(__TempTable[#SALES], __TempTable[Brand], __SelectedBrand, __TempTable[City], __SelectedCity) 
        LOOKUPVALUE(tableSalesByBrandAndCity[#SALES], tableSalesByBrandAndCity[Brand], __SelectedBrand, tableSalesByBrandAndCity[City], __SelectedCity) 
    )

Please see updated pbix attached:
Sales for Brand and City v2.pbix (39.3 KB)

Many thanks

Mark

3

I think I’ve got it, turned out to be much simpler - of course.

Related Sales for Stock Line, based on Brand and City = 
SUMX(factCurrentStock, 
    VAR __SelectedProductID = min(factCurrentStock[ProductID])  -- Get stock records ProductID
    VAR __SelectedStoreID = MIN(factCurrentStock[StoreID])  -- Get stock records StoreID
    VAR __SelectedBrand = LOOKUPVALUE(dimProducts[Brand], dimProducts[ProductID],__SelectedProductID)  -- Get the Brand based on the ProductID
    VAR __SelectedCity = LOOKUPVALUE(dimStores[City], dimStores[StoreId], __SelectedStoreID)  -- Get the City based on the StoreID
    RETURN
    CALCULATE([No of Sales], 
        dimProducts[Brand] = __SelectedBrand, 
        dimStores[City] = __SelectedCity)
)

Alternatives / improvements still welcome :slight_smile: