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 spent way too much time trying to figure this out, and will probably feel very dumb when someone shows me a simple solution.
I have two tables: ChargeTable and CreditTable. The Charge Table has a series of accounts, and each account has a series of systems. Each system has a monthly charge. The CreditTable has a monthly credit amount by account.
I need to determine each system's % of it's respective accounts total charge. Then I need to multiple that % by the credit amount for the same account to determine the credit to apply to each system. Below is a rough sketch of what I'm trying to accomplish.
I appreciate your help!
Solved! Go to Solution.
Hi, @StaceyG
Try to create measures:
% =
SUM('ChargeTable'[Monthly Charge])/CALCULATE(SUM(ChargeTable[Monthly Charge]),ALLEXCEPT('ChargeTable',ChargeTable[Account]))
credit amount =
var _t=
SUMMARIZE(
'ChargeTable',
[Account],[System],[Monthly Charge],"1",[%],"_1",
[%]*MAXX(FILTER(ALL('CreditTable'),'CreditTable'[Account]=MAX('ChargeTable'[Account])),[Monthly Credit])
)
return SUMX(_t,[_1])
system % of total cost =
SUM('ChargeTable'[Monthly Charge])/CALCULATE(SUM('ChargeTable'[Monthly Charge]),ALLSELECTED('ChargeTable'))
Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @StaceyG
Try to create measures:
% =
SUM('ChargeTable'[Monthly Charge])/CALCULATE(SUM(ChargeTable[Monthly Charge]),ALLEXCEPT('ChargeTable',ChargeTable[Account]))
credit amount =
var _t=
SUMMARIZE(
'ChargeTable',
[Account],[System],[Monthly Charge],"1",[%],"_1",
[%]*MAXX(FILTER(ALL('CreditTable'),'CreditTable'[Account]=MAX('ChargeTable'[Account])),[Monthly Credit])
)
return SUMX(_t,[_1])
system % of total cost =
SUM('ChargeTable'[Monthly Charge])/CALCULATE(SUM('ChargeTable'[Monthly Charge]),ALLSELECTED('ChargeTable'))
Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@speedramps thanks for this link! That definitely got me part of the way 🙂 I was able to determine system total of account. Now I am trying to determine the system total of the total cost (all accounts combined). I was able to get the total using CALCULATE(sum(ChargeTable[Monthly Charge]), ALL(Chargetable)), however I am also trying to view the data by a selected date, and I've been unsuccessful incorporating the selected date into the total cost so that I can then determine system % of total cost. Any ideas?
@StaceyG https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071...
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 |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |