Forum Discussion

AlanP514's avatar
AlanP514
Post Patron
4 years ago
Solved

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 



Need Help
Show and Tell
Tips and Tricks
  • tamerj1's avatar
    tamerj1
    4 years ago

    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 )

13 Replies

  • Hariharan_R's avatar
    Hariharan_R
    Solution Sage
    4 years ago

    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's avatar
      AlanP514
      Post Patron
      4 years ago

      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's avatar
        Hariharan_R
        Solution Sage
        4 years ago

        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

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    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's avatar
      AlanP514
      Post Patron
      4 years ago

      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's avatar
        rohit_singh
        Solution Sage
        4 years ago

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

         



  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      AlanP514
      Post Patron
      4 years ago

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

       

       

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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 )