cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Aggregation logic dax

 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

1 ACCEPTED SOLUTION
Super User

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

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

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

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

Solution Sage

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

Helper V

Hai @rohit_singh

this is the way value should be calculated

Helper V

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

Solution Sage

Hi @AlanP514 ,

% 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

Helper V

Hai @rohit_singh

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

Solution Sage

You forgot to return the value @AlanP514

Helper V

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

Solution Sage

Hi Alan,

Try the below expression.

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

Thanks

Hari

If I helped you, click on the Thumbs Up to give Kudos.

Helper V

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

Solution Sage

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.