AverageX with dynamic denominator

[FeroSK]

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 1	Green
Indicator 2	Blue/Pink
Indicator 2	Blue
Indicator 2	Blue/Yellow
Indicator 3	Yellow
Indicator 3	Yellow/Pink
Indicator 1	Blue/Yellow

Indicator Details:

Indicator IDNameDenominator

Green	Name1	Measure A
Green	Name2	Measure A
Green	Name3	Measure B
Blue	Name4	Measure A
Blue	Name5	Measure A
Blue	Name6	Measure A
Blue/Pink	Name7	Measure B
Blue/Pink	Name8	Measure B
Blue/Pink	Name9	Measure C
Blue/Yellow	Name10	Measure B
Blue/Yellow	Name11	Measure B
Yellow	Name12	Measure C
Yellow	Name13	Measure A
Yellow/Pink	Name14	Measure A
Yellow/Pink	Name15	Measure B

[FeroSK]

please, someone help

[FeroSK]

sample PBIX is in first message

[Greg_Deckler]

@FeroSK One thought, try DISTINCT vs VALUES

[FeroSK]

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