topic Re: Struggling with simple calculation & context transition in DAX Commands and Tips

Struggling with simple calculation & context transition

Anonymous — Fri, 18 Oct 2024 06:43:24 GMT

Hi,

I've reached a mental roadblock. Any help would be greatly appreciated. Here is a sample file with my issue: Example_Conext_Transition.pbix

My data model consists of a fact table with observations, a date dimension an category dimension.

The sample data looks like this:

I want to calculate the following:

For each observation in the fact table sum its value divided by the number of observations of the same group within the selected time period (detailed example below).

This is the DAX formula I tried:

Sum averaged Value = SUMX(FactObservations, FactObservations[Value] / CALCULATE( [# Observations], REMOVEFILTERS(FactObservations), VALUES(FactObservations[Group]), VALUES('Date'[Month]) ) )

while "# Observations" is just:

COUNTROWS(FactObservations)

To test if the observations are counted corectly I also created a measure "SUMX group count" which is basically the same formula as "Sum aggregated value" just without the division:

SUMX( FactObservations, CALCULATE( [# Observations], REMOVEFILTERS(FactObservations), VALUES(FactObservations[Group]), VALUES('Date'[Month]) ) )

In the screenshot below you can see, that I selected Month 1 in the slicer and the "SUMX group count" measure correctly counts the number of "Group 1" observations in this month (which is 2).
Furthermore it correctly calculates the averaged value, so for example in the first row: Value 10 / 2 observations = 5.

However, if I try to aggregate the "Sum averaged Value" in the donut chart by Category (see red highlights) I would expect the value for Category A to be 5 + 10 + 7,5 = 22.5 . Instead the value is 11.25 - so exactly half.

What am I doing wrong? How should the Dax formula look like and why?

Thank you for your support. I can upload the sample file if needed.
Best regards

Re: Struggling with simple calculation & context transition

lbendlin — Thu, 17 Oct 2024 22:20:44 GMT

You omitted quite a few details. What's your formula for [# Observations] ? Something like this?

# Observations = CALCULATE(COUNTROWS(FactObservations),ALLEXCEPT(FactObservations,FactObservations[Group]))

What is your formula for SUMX Group Count? Something like this?

SUMX group count = var g = ADDCOLUMNS(VALUES(FactObservations[Group]),"cr",var gr=[Group] return COUNTROWS(filter(ALLSELECTED(FactObservations),[Group]=gr))) return sumx(g,[cr])

Note that the totals on your version are incorrect.

It is generally not advisable to use columns from your fact table for visuals. You are missing a Groups dimension table.

Re: Struggling with simple calculation & context transition

Anonymous — Fri, 18 Oct 2024 06:46:14 GMT

Thank you for the feedback. I added the formulas to my original post. I also added a sample file.
What might be the correct dax formula?
Thanks for your support

Re: Struggling with simple calculation & context transition

lbendlin — Fri, 18 Oct 2024 16:09:15 GMT

Your SUMX Group Count totals are incorrect. Should be 5, not 25

You are walking on very thin ice here. Usually attributes from the fact table should not be used for grouping.

Re: Struggling with simple calculation & context transition

Anonymous — Wed, 23 Oct 2024 08:59:18 GMT

Thank you for your effort. In your example the count is correct, however the more relevant measure "Sum averaged values" is not. For category A it should be 22.5 and not 30.

Furthermore I feel like the measures you propose are too complex for the simple calculation which I want to achieve. I guess that I haven't explained my issue accurately.

My main goal is to:
For each observation in the fact table sum the value in colum Value divided by the number of observations with the same value in the Group column within a selected time period (for example by an external slicer)

Maybe I need to do some more experimentation myself and try to explain my issue better.
Nevertheless, thank you for your support.