Calculating GEOMEAN on roll up and roll down basis based on several columns

[kamran_imam]

Hi everyone,

Apologies for a lengthy question but I got stuck badly in a project. I am trying to calculate GEOMEAN of column, based on other columns on a roll up and roll down basis. To elaborate further, I have a table with following columns and I am trying to calculate some price movements. I have a table with following columns:

Date, Brand, Product Category Code, Product Category Description, Prod Sub Category, Prod Sub Category Description, City, Region, Province/State and Relative. All columns have "text" data type except for Date (Date type) and Relative (integer).

Example of the data

Example Data 

What I want is to calculate the GEOMEAN of relative column at each timestep and with respect to each entry in Brand, Product Category Code, Product Category Description, Prod Sub Category, Prod Sub Category Description, City, Region, Province/State in such a way that in a visual, if I want to drill down to Product Sub Category Code level, it will calculate a GEOMEAN of each product sub category level for each, city, region state etc. Similarly, if I select a higher level hierarchy i.e. Product Category Code, it gives a GEOMEAN of all Product Sub Categories within the Product Category for each city region state etc. Similarly, it gives me GEOMEAN if I want to roll up to State level or want to roll down to city level.

Once, I have the GEOMEAN in the manner described above for a given Date, I would then need it to multiply with the GEOMEAN of the previous Date.

I am using the following code to calculate the desired measure

measure = 

Calculate (GEOMEAN('Table'[Relative]),

FILTER(ALLEXCEPT(

'Table',

'Table'[Brand], 'Table'[Product Category Code], 'Table'[Product Category Description],

'Table'[Product Sub Category Code], 'Table'[Product Sub Category Description], 'Table'[City], 'Table'[Region],

'Table'[Province/State]),

'Table'[Date] = Max('Table'[Date])))

I then try to multiply the measure calculated at a given time step with the previous time step by using following code.

desired_results = 

Calculate (Product('Table'[measure]),

FILTER(ALLEXCEPT(

'Table',

'Table'[Brand], 'Table'[Product Category Code], 'Table'[Product Category Description],

'Table'[Product Sub Category Code], 'Table'[Product Sub Category Description], 'Table'[City], 'Table'[Region],

'Table'[Province/State]),

'Table'[Date] <= Max('Table'[Date])))

But I don't get the desired result because, function Product does not take measure as an input and when I try to calculate a column in the first step instead of measure I get blanks. Not sure how to get around the issue. Your help would be greatly appreciated.

Thanks

[v-rzhou-msft · Accepted solution]

Hi @kamran_imam ,

I think your [desired_results] measure is close. You get error due to you add a measure into PRODUCT function. It needs a column instead a measure. You can try PRODUCTX to achieve your goal.

desired_results = 
CALCULATE (
    PRODUCTX ( VALUES ( 'Table'[Date] ), [Measure] ),
    FILTER (
        ALLEXCEPT (
            'Table',
            'Table'[Brand],
            'Table'[Product Category Code],
            'Table'[Product Category Description],
            'Table'[Prod Sub Category Code],
            'Table'[Prod Sub Category Description],
            'Table'[City],
            'Table'[Region],
            'Table'[Province/State]
        ),
        'Table'[Date] <= MAX ( 'Table'[Date] )
    )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kamran_imam]

Thanks Rico! It worked 🙂

[kamran_imam]

Thanks for your question Alexis. Let me elaborate further regarding current and previous time step. So for example, if I am looking at the month of April 2021, then April 2021 is current time step and March 2021 is previous time step. So the GEOMEAN of Relative column for April 2021 gets multiplied by GEOMEAN of Relatives from March 2021 and in the previous time step, GEOMEAN of March 2021 was multiplied by GEOMEAN of Februrary 2021.

So in February the result would be GEOMEAN[RELATIVE in Jan 2021] * GEOMEAN[RELATIVE in Feb 2021] = X1.

In March the result would be GEOMEAN[RELATIVE in Mar 2021] * X1 = X2

In April the result would be GEOMEAN[RELATIVE in Apr 2021]*X2 = X3

.....

Example.xlsx 

Apologies about the link not working. You may try it now. Appreciate your help.

[AlexisOlson]

How about this?

[GeoMean] * CALCULATE ( [GeoMean], DATEADD ( 'Table'[Date], -1, MONTH ) )

[kamran_imam]

Its actually close but not exactly what I am looking for. Let me elaborate below.

Date	Relative	Result I am Getting	Result I want
Jan 21	1
Feb 21	1	1	1
Mar 21	1.2	1.2	1.2
Apr 21	1	1.2	1.2
May 21	1	1	1.2

So basically, the current month value in column "Relative" needs to be multiplied with its previous month value and "stored" in the "Desired Result" column for the current month, then the next month value in the column Relative needs to be multiplied with previous month 'Desired Result' value. If you focus on the month of May, that's the problem I am having. Appreciate if you could help resolve it.

[AlexisOlson]

Ah, you're defining it recursively so you do need a cumulative calculation.

Try this:

VAR CurrMonth = MAX ( 'Table'[Date] )
RETURN
    PRODUCTX (
        CALCULATETABLE ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] <= CurrMonth ),
        VAR MonthDiff = DATEDIFF ( 'Table'[Date], CurrMonth, MONTH )
        RETURN
            CALCULATE ( [GeoMean], DATEADD ( 'Table'[Date], MonthDiff, MONTH ) )
    )

[kamran_imam]

So the following DAX code worked for me.

VAR CurrMonth = MAX ( 'Table'[Date] )
RETURN
    PRODUCTX (
        CALCULATETABLE ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] <= CurrMonth ),
            CALCULATE ( [GeoMean])
    )

Your suggestion for using PRODUCTX worked for me. Thanks a lot for your help!

[AlexisOlson]

Ah. Good call. You don't need the DATEADD if you use the date row context.

[AlexisOlson]

What do you mean by "previous time step"? Your desired_results measure looks to be including all dates up to and including the current time step. What is the purpose of your intended desired measure?

FYI, I cannot access the example data you link to.