Forum Discussion
Dax Aggregation for Country level
Hai All, I need one help on this
The above table % Share range is the expecting output
I want to achieve this logic at the Dax level
Users want to see the % total at the country level
For eg: For DE (18+13+10+2+22) = 65
18/65*100 = 27.69 For DE For "above target" category
and for the model number calculation i used DISTINCT COUNT and model number column is the test data type
Please help me to achieve this logic in dax level
Hi Anonymous
Please use the following code
% Share Range = VAR Numerator = [# Model Number] VAR T1 = CALCULATETABLE ( Initiative, ALLEXCEPT ( Initiative, Initiative[Country] ) ) VAR T2 = SUMMARIZE ( T1, Initiative[Country], Initiative[Category] ) VAR T3 = ADDCOLUMNS ( T2, "Models", [# Model Number] ) VAR Denominator = SUMX ( T3, [Models] ) RETURN IF ( HASONEVALUE ( Initiative[Country] ), DIVIDE ( Numerator, Denominator ), 1 )
2 Replies
- themistoklis
Community Champion
Hello Anonymous
Create a new measure and add the following formula:
% Share = VAR Volume = SUM('Table'[Model Number]) VAR AllVolume = CALCULATE ( SUM('Table'[Model Number]),ALLSELECTED('Table'[Category]) ) RETURN DIVIDE ( Volume, AllVolume )Fie also attached
- tamerj1
Hi Anonymous
Please use the following code
% Share Range = VAR Numerator = [# Model Number] VAR T1 = CALCULATETABLE ( Initiative, ALLEXCEPT ( Initiative, Initiative[Country] ) ) VAR T2 = SUMMARIZE ( T1, Initiative[Country], Initiative[Category] ) VAR T3 = ADDCOLUMNS ( T2, "Models", [# Model Number] ) VAR Denominator = SUMX ( T3, [Models] ) RETURN IF ( HASONEVALUE ( Initiative[Country] ), DIVIDE ( Numerator, Denominator ), 1 )
