Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.