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
    Icon for Impactful Individual rankImpactful 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
        Icon for Impactful Individual rankImpactful 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.