topic Re: Calculating GEOMEAN on roll up and roll down basis based on several columns in DAX Commands and Tips

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

Anonymous — Fri, 22 Apr 2022 20:10:13 GMT

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 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

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

AlexisOlson — Sun, 24 Apr 2022 17:59:43 GMT

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.

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

Anonymous — Mon, 25 Apr 2022 16:08:02 GMT

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.

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

AlexisOlson — Mon, 25 Apr 2022 16:39:08 GMT

How about this?

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

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

Anonymous — Mon, 25 Apr 2022 19:53:07 GMT

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.

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

AlexisOlson — Mon, 25 Apr 2022 21:47:04 GMT

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 ) ) )

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

Anonymous — Wed, 27 Apr 2022 07:25:49 GMT

Hi Anonymous ,

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.

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

Anonymous — Wed, 27 Apr 2022 17:13:15 GMT

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!

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

Anonymous — Wed, 27 Apr 2022 17:14:28 GMT

Thanks Rico! It worked 🙂

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

AlexisOlson — Wed, 27 Apr 2022 19:00:26 GMT

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