Skip to main content
cancel
Showing results for 
Search instead 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

Reply
AlanP514
Helper V
Helper V

Aggregation logic dax

InputOutput
MarketExecutive Relevance Category# Model Number(Count)% Share Range
DEAbove target1827.69%
DEMiddle target1320.00%
DELow target1015.38%
DEHigh target23.08%
DEAbove target2233.85%
FRMiddle target3170.45%
FRLow target613.64%
FRHigh target24.55%
FRAbove target511.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
tamerj1
Super User
Super User

@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 )

View solution in original post

13 REPLIES 13
tamerj1
Super User
Super User

@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
Super User
Super User

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 )

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

AlanP514_2-1655310440352.png

 

 

rohit_singh
Solution Sage
Solution Sage

Hello @AlanP514 ,

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

rohit_singh_0-1655307853033.png

% 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! 🙂

Hai @rohit_singh 

AlanP514_3-1655310491401.png

 

this is the way value should be calculated

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

AlanP514_0-1655308398309.png

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 

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! 🙂

 



Hai @rohit_singh 

AlanP514_0-1655310988895.png

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

You forgot to return the value @AlanP514 

rohit_singh_0-1655311116426.png

 

AlanP514_0-1655311366568.png

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

AlanP514_1-1655311451758.png

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






Hariharan_R
Solution Sage
Solution Sage

Hi Alan,

Try the below expression.

 

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

 

Hariharan_R_0-1655306434712.png

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


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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.