Percentage calculation for multiple months

[ashishtele]

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 ?

[fbrossard]

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]

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 · Accepted solution]

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]

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