Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am trying to calculate percentage of totals by year.
For example
Contract Year Policy Type Amount %
No. 1 2016 Default 50,000 40%
No. 2 2016 Default 75,000 60%
Total 125,000 100%
No. 3 2017 Default2 60,000 50%
No. 4 2017 Default2 60,000 50%
Total 120,000 1005
I've tried DIVIDE(SUM(Amount), CALCULATE(SUM(Amount), ALLSELECTED(Year))
The context has to be wrong since the value that is returned is 1.0
Solved! Go to Solution.
try it:
CALCULATE(SUM(Table1[Amount]) / CALCULATE(SUM(Table1[Amount]); ALLEXCEPT(Table1; Table1[Year]; Table1[PolicyType])))
Hi @tlenzmeier,
Try this code:
% Per Year = DIVIDE(CALCULATE(SUM(Table1[Amount]); ALLEXCEPT(Table1; Table1[Year])); CALCULATE(SUM(Table1[Amount]); ALLSELECTED(Table1)))
Ricardo,
Thank you for your reply. I neglected to mention that I need the percentages by policy type and year. So for year 1, policy type = default, I need 60% and 40% and for default1, I need 50% and 50%
try it:
CALCULATE(SUM(Table1[Amount]) / CALCULATE(SUM(Table1[Amount]); ALLEXCEPT(Table1; Table1[Year]; Table1[PolicyType])))
This worked! I only had to add one more column to the ALLEXCEPT; namely VendorID.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
86 | |
77 | |
60 | |
58 | |
58 |
User | Count |
---|---|
151 | |
113 | |
99 | |
80 | |
72 |