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êsUser | Count |
---|---|
94 | |
86 | |
78 | |
69 | |
63 |
User | Count |
---|---|
113 | |
99 | |
97 | |
64 | |
59 |