Forum Discussion
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:
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 |
4 Replies
- Greg_Deckler
Community Champion
FeroSK One thought, try DISTINCT vs VALUES
- FeroSKFrequent 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
- FeroSKFrequent Visitor
sample PBIX is in first message
- FeroSKFrequent Visitor
please, someone help