Forum Discussion

Osalah's avatar
Osalah
Regular Visitor
8 years ago
Solved

Calculation on Grouped Columns

Hi,

I've grouped my data according to months of record creation date (I have a field "CreatedMonth").

Then for each month I count the SLA Status (false and True)

It looks like this:

Now I would like for each row to show a percentage calculation of the count in "False" over the row total.

Appreciate help in accomplishing this.

thanks

Osama S.

  • Select the down arrow for your Value in the visual (SLA count?) and then select "Show as % of row total".  That will convert the visual to a % of False and True for each CreatedMonth.

  • tjd's avatar
    tjd
    8 years ago

    First, you're making this too complicated.  You only need three measures:

         RowCount = Number of rows in the Incidents2017 table (assuming they each have either a True or False Overdue Status.  This value will get filtered by whatever categorical value you have in a column (Overdue Status for you and will result in two measures -one True and one False count).

         Total Sum of  RowCount = Calculate (RowCount, ALLSELECTED) This value is not filtered by a categorical vaule (ALLSELECTED) and results in the sum of RowCount (for False) and RowCount (for True).

         %Distribution = Divide(RowCount, Total Sum of RowCount, 0) This value becomes your percentage distribution.

     

    Then set your graph up as 

         Axis: CreatedMonth

         Value: %Distribution (will filter based on what you put in legend)

         Legend: Overdue Status (True/False will result automatically if that's all that's in the Overdue Status column

     

    Your graph will default to a 100% stacked bar as that's what you're asking it to do - show me the % distribution of overdue status by each month created.  The % True and % False always sums to 100% for each month.

     

    For what you're trying to do, I would show the % as a tooltip and use RowCount as the Value. Then the graph will show correctly based on the count sum and % of each bar portion will be shown in the tooltip when you hover over it.

     

    Hope this helps.

6 Replies

  • tjd's avatar
    tjd
    Impactful Individual

    Select the down arrow for your Value in the visual (SLA count?) and then select "Show as % of row total".  That will convert the visual to a % of False and True for each CreatedMonth.

    • Osalah's avatar
      Osalah
      Regular Visitor

      Thanks! that was easy :)

      I'm trying to do that same with a stacked bar chart, but there I can't find something to do percentages "per bar" and only "percent of grand total".

      Any ideas?

      • tjd's avatar
        tjd
        Impactful Individual

        In this case, you're going to have to manually calculate (DAX) the measure.  You'll need the sum of all Selected Values and then the %Distribution = Selected Value/Sum of all Selected Values.  That translates to Selected Value (True or False) and Sum of all Selected Values (True AND False).  In the case of Created Month 1 (217 True, 37 False), that means you'd have:

             For True: 217/254 = 85.4%

             For False 37/254 = 14.6%

        You would enter the %Distribution as the value for the graph.