Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a very simple problem that I'm strugling to resolve.
I have a follwing Table Called Payroll
Month ------ Dept ------- Amount
1-2019------ Fin ------- 50
1-2019------ Hr ------- 35
1-2019------ Mkt ------- 15
2-2019------ Fin------- 20
2-2019------ Hr------- 55
2-2019------ Mkt------- 25
I need follwing result
Month ------ Dept ------- Amount ---- Percent
1-2019------ Fin ------- 115 ---- 69% (Becuase 115 of 165 total of 1-2019)
1-2019------ Hr ------- 35 ------ 21.21% (Becuase 35 of 165 total of 1-2019)
1-2019------ Mkt ------- 15------ 9.09% (Becuase 15 of 165 total of 1-2019)
2-2019------ Fin------- 120 -- so on
2-2019------ Hr------- 25 -- so on
2-2019------ Mkt------- 20 -- so on
So basically, I want to get % at each month level.
Thanks
Solved! Go to Solution.
Hi @sabeensp ,
You need to add the following measure to your model:
% over month = SUM ( Payroll[Amount] ) / CALCULATE ( SUM ( Payroll[Amount] ); ALLEXCEPT ( Payroll; Payroll[Month] ) )
Another option is this:
% over month =
SUM ( Payroll[Amount] )
/ CALCULATE (
SUM ( Payroll[Amount] );
ALL ( Payroll[Month]; Payroll[Dept] );
VALUES ( Payroll[Month] )
)
Both give the same result but based on the size of your table they can provide different performance.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @sabeensp ,
You need to add the following measure to your model:
% over month = SUM ( Payroll[Amount] ) / CALCULATE ( SUM ( Payroll[Amount] ); ALLEXCEPT ( Payroll; Payroll[Month] ) )
Another option is this:
% over month =
SUM ( Payroll[Amount] )
/ CALCULATE (
SUM ( Payroll[Amount] );
ALL ( Payroll[Month]; Payroll[Dept] );
VALUES ( Payroll[Month] )
)
Both give the same result but based on the size of your table they can provide different performance.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
109 | |
101 | |
84 | |
79 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |