Determine % of account total

[StaceyG]

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!

[v-angzheng-msft · Accepted 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.

[speedramps]

Try this.  

https://community.powerbi.com/t5/Desktop/Individual-Tank-volume-as-a-of-total-Tank-volume/m-p/212759...

[StaceyG]

@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?

[smpa01]

@StaceyG  https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071...