Forum Discussion

ashishtele's avatar
ashishtele
Frequent Visitor
10 years ago
Solved

Percentage calculation for multiple months

Hello,

 

I have data table with three columns Employee, month ,B and C. I have to calculate B/C (B divide by C) for each employee and month. I have created new coulmn = B/C and it is working fine for one month at a time. But when I select more than two months at a time, it sums up the calculated percentage values. How should I proceed so that it will add the values for B and C first for multiple months and then divide them ?

Need Help
  • greggyb's avatar
    greggyb
    10 years ago

    The following measures achieve what you want.

     

    SumB =
SUM( 'MyTable'[B] )     // It's considered best practice to always use
                                    // fully qualified column names in Table[Column]
                                    // format.

SumC =
SUM( 'MyTable[C] )

BoverC =
DIVIDE( [SumB], [SumC] )      // It is considered best practice to use
                                                // only the measure name (without table
                                                // reference) when referring to measures

    CALCULATE() would only be necessary if you want to write code to manipulate the filter context for the measure.

     

    If you use BoverC in a report now, you can put it in the Value(s) area of a visualization. Putting months on the axis would give you BoverC by month. If you put BoverC in a visualization alone and use month as a slicer you will see the appropriate behavior when you select a subset of months.

4 Replies

  • fbrossard's avatar
    fbrossard
    Kudo Commander
    10 years ago

    Hi,

    You dont' have to create a new calculated column but only a new measure.

    In PowerPivot/SSAS Tabular you have when you add a calculated column, it's been calculated for each row when you refresh you model, and then you can make aggreation on it => you are in the row context.

    For your needs, you have to create a new measure. This measure is calculated on the fly depending the attributes and filter you use.

    For more detail read this : https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

    • ashishtele's avatar
      ashishtele
      Frequent Visitor
      10 years ago

      Hi,

       

      I tried with =>

      =calculate(sum(B)/sum(c),Month) but it is not giving desired result.

      Also, I am selecting different values of month from 'Slicer' visualization. So I want measure to take selected months dynamically on runtime and calculate the values.

      • greggyb's avatar
        greggyb
        Resident Rockstar
        10 years ago

        The following measures achieve what you want.

         

        SumB =
SUM( 'MyTable'[B] )     // It's considered best practice to always use
                                    // fully qualified column names in Table[Column]
                                    // format.

SumC =
SUM( 'MyTable[C] )

BoverC =
DIVIDE( [SumB], [SumC] )      // It is considered best practice to use
                                                // only the measure name (without table
                                                // reference) when referring to measures

        CALCULATE() would only be necessary if you want to write code to manipulate the filter context for the measure.

         

        If you use BoverC in a report now, you can put it in the Value(s) area of a visualization. Putting months on the axis would give you BoverC by month. If you put BoverC in a visualization alone and use month as a slicer you will see the appropriate behavior when you select a subset of months.