Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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...