Aggregation logic dax

[AlanP514]

Input	Output
Market	Executive Relevance Category	# Model Number(Count)	% Share Range
DE	Above target	18	27.69%
DE	Middle target	13	20.00%
DE	Low target	10	15.38%
DE	High target	2	3.08%
DE	Above target	22	33.85%
FR	Middle target	31	70.45%
FR	Low target	6	13.64%
FR	High target	2	4.55%
FR	Above target	5	11.36%

Hai All, 
Requirement: I want to get the percentage of the model for each market based on the total of that particular market
For eg: FR (Market) Total model number count is  44  Based on this total number I want to achieve a percentage of each category
High target, low target, above target)  (31/44)*100 = 70.45%
Above i mentioned expected output

note: If a new category is  added in the future that also should apply the same logic;
Thanks 
Alan 

[tamerj1 · Accepted solution]

@AlanP514 

Ok. Please try

% Share Range =
VAR Numerator =
    DISTINCTCOUNT ( TableName[Model Number] )
VAR Denominator =
    CALCULATE (
        DISTINCTCOUNT ( TableName[Model Number] ),
        ALL ( TableName[Relevance Category] )
    )
RETURN
    DIVIDE ( Numerator, Denominator )

[tamerj1]

Hi @AlanP514 

please try

% Share Range =
VAR Numerator =
    DISTINCTCOUNT ( TableName[Model Number] )
VAR Denominator =
    CALCULATE (
        DISTINCTCOUNT ( TableName[Model Number] ),
        ALLEXCEPT ( TableName, TableName[Market] )
    )
RETURN
    DIVIDE ( Numerator, Denominator )

[AlanP514]

Hai @tamerj1 
The values are wrong based on this dax measure I am attaching SC With this please check this 

[rohit_singh]

Hello @AlanP514 ,

Please try creating this measure. It gives the expected output.

% Share Range =

var _total =

CALCULATE(

sum(Markets[# Model Number(Count)]),

ALLEXCEPT(Markets, Markets[Market])

)

var _target =

DIVIDE(SUM(Markets[# Model Number(Count)]), _total, 0)

Return

_target

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[AlanP514]

Hai @rohit_singh 

this is the way value should be calculated

[AlanP514]

Hai @rohit_singh,I have a model number but that is a fully text data type so I cannot sum

This is the measure and this column consist of text values so i am taking distinct count 
can you change your measure based on this 

[rohit_singh]

Hi @AlanP514 ,

Please try this :

% Share Range =

var _total =
       SUMX(
            ALLEXCEPT(Markets, Markets[Market]),
            [# Model Number]
)

var _target =
        DIVIDE(SUMX(Markets,[# Model Number]), _total, 0)

Return

_target

Here [# Model Number] is your measure.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[AlanP514]

Hai @rohit_singh 

This is what i am getting based on the above measure what's the error

[rohit_singh]

You forgot to return the value @AlanP514 

[AlanP514]

Hai @rohit_singh  This is the actual value that should return but based on your dax I am getting this 
I am attaching under

These values are wrong please look above attached image and way i want to calculate

[Hariharan_R]

Hi Alan,

Try the below expression.

Result =
DIVIDE (
    SUM ( Market[Model] ),
    CALCULATE ( SUM ( Market[Model] ), ALL ( Market[Executive] ) )
)

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

My Blog :: YouTube Channel :: My Linkedin

[AlanP514]

Hai @Hariharan_R  Model number is a text field
i am expecting output like this 
For eg: FR (Market) Total model number count is  44(only for FR)  Based on this total number I want to achieve a percentage of each category
High target, low target, above target)  (31/44)*100 = 70.45%,for middle target  (6/44)*100 = 13.64%, FOR Low target
Above I mentioned the expected output

[Hariharan_R]

Hi,

You need to get number value from Text data type for this scenario so please change the data type. If you see my result, it is already showing correct values

Thanks

Hari