Forum Discussion

FeroSK's avatar
FeroSK
Frequent Visitor
3 years ago

AverageX with dynamic denominator

Hi Friends

 

My snowflake data model contains many-to-many relationships

This is intentional because I want to use multiple values from the fact table to calculate separate indicators.

I need to use AverageX because I need to divide each Indicator[Name] by the denominator given in the Indicator Details[Denominator] table

 

The problem is in the dynamic selection of the denominator according to the Denominator column in the Indicator detail table. Could someone help me solve this?

 

Thank you

 

with only one selected denominator:

 


PBIX to download 

 

 

 

 

Total Count = 
COUNTROWS( 'Fact Table' )
Average = 
    AVERAGEX(
        VALUES('Indicator Details'[Name]),
            [Total Count] /
            var _denominator = SELECTEDVALUE( 'Indicator Details'[Denominator] ) 
            var _denominatorValue = 
               SWITCH( 
                TRUE(),
                _denominator = "Measure A", [Denominator 1],
                _denominator = "Measure B", [Denominator 2],
                _denominator = "Measure C", [Denominator 3]
               )
            return
            _denominatorValue
    )

 

 

 

 

This is example only:

 

 

 

Denominator 1 = 10 
Denominator 2 = 20
Denominator 3 = 30

 

 

 

 

 

Indicator table:
IndicatorIndicator ID

Indicator 1Green
Indicator 2Blue/Pink
Indicator 2Blue
Indicator 2Blue/Yellow
Indicator 3Yellow
Indicator 3Yellow/Pink
Indicator 1Blue/Yellow

 

Indicator Details:

Indicator IDNameDenominator

GreenName1Measure A
GreenName2Measure A
GreenName3Measure B
BlueName4Measure A
BlueName5Measure A
BlueName6Measure A
Blue/PinkName7Measure B
Blue/PinkName8Measure B
Blue/PinkName9Measure C
Blue/YellowName10Measure B
Blue/YellowName11Measure B
YellowName12Measure C
YellowName13Measure A
Yellow/PinkName14Measure A
Yellow/PinkName15Measure B

 

4 Replies

    • FeroSK's avatar
      FeroSK
      Frequent Visitor

      Sorry, did not help. Problem is in this denominator part:

                  var _denominator = 
                      LOOKUPVALUE( 
                          'Indicator Details'[Denominator], 
                          'Indicator Details'[Denominator], 
                          SELECTEDVALUE( 'Indicator Details'[Denominator] ) 
                      )
                  var _denominatorValue = 
                     SWITCH( 
                      TRUE(),
                      _denominator = "Measure A", [Denominator 1],
                      _denominator = "Measure B", [Denominator 2],
                      _denominator = "Measure C", [Denominator 3]
                     )
                  return
                  _denominatorValue