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 f...
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.

fbrossard's avatar
fbrossard
Icon for Kudo Commander rankKudo 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
      Icon for Resident Rockstar rankResident 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.

      • itchyeyeballs's avatar
        itchyeyeballs
        Icon for Impactful Individual rankImpactful Individual
        10 years ago

        Using the divide function as shown by greggyb will automatically handle division by 0 errors so is recomended instead of using "/"