Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ?
Solved! Go to 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.
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/
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.
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.
Using the divide function as shown by @greggyb will automatically handle division by 0 errors so is recomended instead of using "/"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |