topic Re: DAX to measure Same Store Sale in DAX Commands and Tips

DAX to measure Same Store Sale

Anonymous — Fri, 28 Jun 2024 20:12:57 GMT

Hello everyone!

I am having trouble writing a DAX formula in Power BI to calculate Same Store Sales (SSS). My calculation of SSS is (CURRENT_VALUE_ / PREVIOUS_VALUE_) - 1.

The problem is that, when looking at the CLIENT_ID hierarchy in a matrix visual in Power BI, I want the SSS measure to consider only the CURRENT_VALUE_ of the brands that also had sales in PREVIOUS_VALUE_.

For example, in the figure below, I want CLIENT_ID 123456 to show me an SSS of 27% in total, instead of 100%. That is, I want it to consider (140 / 110) - 1, instead of (220 / 110) - 1, as it should only sum the CURRENT_VALUE_ of the brands that also have PREVIOUS_VALUE_ (that is, only the GREEN and PINK brands).

My current DAX formula is as follows:

VAR tempTable = SUMMARIZE(clients, clients[CLIENT_ID], "PER_ATUAL", [CURRENT_VALUE_] ,"PER_ANTER", [PREVIOUS_VALUE_])
VAR currentValue = SUMX(FILTER(tempTable, NOT ISBLANK([PER_ATUAL]) && NOT ISBLANK([PER_ANTER])), [PER_ATUAL])
VAR previousValue = SUMX(FILTER(tempTable, NOT ISBLANK([PER_ATUAL]) && NOT ISBLANK([PER_ANTER])), [PER_ANTER])

RETURN
IF(DIVIDE(currentValue, previousValue, BLANK()) -1 = -1 ,
BLANK(),
DIVIDE(currentValue, previousValue, BLANK()) -1
)

Any help or tips?

Re: DAX to measure Same Store Sale

Jihwan_Kim — Sat, 29 Jun 2024 08:21:24 GMT

Hi,

I am not sure how you semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Current sales: = SUM( sales[sales] )

Previous sales: = CALCULATE ( [Current sales:], OFFSET ( -1, ALL ( 'year'[year] ), ORDERBY ( 'year'[year], ASC ) ) )

Expected result measure: = VAR _t = FILTER ( ADDCOLUMNS ( SUMMARIZE ( sales, client[client_id], store[store_id], 'year'[year] ), "@current", [Current sales:], "@prev", [Previous sales:] ), NOT ISBLANK ( [@prev] ) ) VAR _result = DIVIDE ( SUMX ( _t, [@current] ), SUMX ( _t, [@prev] ) ) - 1 RETURN IF ( NOT ISBLANK ( [Previous sales:] ), _result )

Re: DAX to measure Same Store Sale

Anonymous — Mon, 01 Jul 2024 16:04:53 GMT

It worked perfectly. Thank you so much!!