Forum Discussion

tviz21's avatar
tviz21
New Member
10 years ago

COUNT and PERCENT over specific ROW

I am having a hard time figuring out how to do a simple % over a specific row in a Matrix - thought it would just be a simple calculation but apparently it's not that easy?

 

I have a matrix with 3 rows -

Category

Comment1

Comment2

 

Value is 

COUNT of 'Type'

 

I just want to show COUNT of 'Type' as a % of only the 'Comment1' Row total - how can I do that one? I could not find any examples of this unless I'm searching incorrectly.

Thanks in advance for any help with this, new to Power BI

 

Toni

 

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    tviz21 - Perhaps a Measure such as:

     

    Measure = CALCULATE(VAR Denominator = COUNTX(ALL(types),[Type])
VAR Numerator = COUNT([Type])
RETURN Numerator/Denominator)

     Make sure to format as %.

     

    tviz21 - For clarity, my table is called "types" and I have a single column in that table called "Type". I put the following data in it:

    Type

    1

    2

    3

    2

    3

     

    My matrix shows:

    Type      Measure

    1            20%

    2            40%

    3            40%

     

     

     

     

    • tviz21's avatar
      tviz21
      New Member
      10 years ago

      After playing around with this for a while, I ended up with -

       

      Percent = CALCULATE(VAR Denominator = COUNTROWS(ALL('type'[Types])) VAR Numerator = COUNTA('type'[Types]) RETURN Numerator/Denominator
      )

       

      This gave me an overall percentage which I can filter for the different dimensions I have on my dashboard.

       

      Thanks to both of you for your help! I'll definitely spend more time with both solutions to understand them better as I'm sure I'll need them later.

       

      Toni

      • greggyb's avatar
        greggyb
        Resident Rockstar
        10 years ago

        No need for CALCULATE() or variables in that measure. It should be identical to the much cleaner:

        Percent =
COUNTA( 'type'[Types] ) / COUNTROWS( ALL( 'type'[Types] ) )

        CALCULATE() is only used to modify filter context or transform row context to filter context. Since this is a measure, there is already filter context coming in from the report and the visual's labels, and there is no row context in a measure, except within an iterator function.

         

        The variables are likewise superfluous, because there is no alternate context in which you reference them. If a variable is defined in the same context as it is referenced in, then it's exactly the same as just using the variable definition in-line.

  • MattAllington's avatar
    MattAllington
    Community Champion
    10 years ago

    The Matrix is just the visualisation.  The DAX formula will depend on the table structure in the data model.

     

    So, assuming you have a table called 'Data' with a column called [Type], you could write these 2 DAX Measures

     

    Line Count = countrows(Data[Type])
 
% of Comment 1 = divide([Line Count],calculate([line Count],data[type]="Comment 1"))