I have the following measure which is returning 100% for every row.
Voucher Spend as Pct of GL:=
[Voucher Amt] / CALCULATE ( [Voucher Amt], ALLSELECTED ( Vouchers[Account] ) )
Voucher Amt:=SUM(Vouchers[Voucher Amount])
What I would like is for when I select certain accounts that the measure return what % of the visible total is showing.
At the top level I have an 'Account Tree' lookup table with an 'Account' column all the organization's GL accounts. A column in this table, NODE 7, identifies the GL accounts for Medical Supplies, Dietary Supplies, and so on. I have a slicer selecting "Medical Supplies" to give me the preferred list of GL accounts.
In between I have the 'Purchase Order' table. Each line of a PO has a GL Account, to which the 'Account Tree' links the Account field.
Then comes the Voucher table linked many:1 with the PO table. The Voucher table also has an Account field and the 'Voucher Amount' field with the spend on the PO item.
So when I select the "Medical Supplies" NODE 7 category in the slicer I want to list all the Medical Supply GL Accounts, their Voucher Amt, and the % of the overall Voucher spend on Medical Supply accounts. So I would like to get:
<tbody>
</tbody>
I have tried changing the ALLSELECTED ( ) portion of the CALCULATE statement to do the 'Account Tree'[Account], 'Purchase Orders'[Account], and 'Voucher'[Account] but in all cases each row returns 100% so I'm obviously not understanding the correct use.
Thanks for your help.
Voucher Spend as Pct of GL:=
[Voucher Amt] / CALCULATE ( [Voucher Amt], ALLSELECTED ( Vouchers[Account] ) )
Voucher Amt:=SUM(Vouchers[Voucher Amount])
What I would like is for when I select certain accounts that the measure return what % of the visible total is showing.
At the top level I have an 'Account Tree' lookup table with an 'Account' column all the organization's GL accounts. A column in this table, NODE 7, identifies the GL accounts for Medical Supplies, Dietary Supplies, and so on. I have a slicer selecting "Medical Supplies" to give me the preferred list of GL accounts.
In between I have the 'Purchase Order' table. Each line of a PO has a GL Account, to which the 'Account Tree' links the Account field.
Then comes the Voucher table linked many:1 with the PO table. The Voucher table also has an Account field and the 'Voucher Amount' field with the spend on the PO item.
So when I select the "Medical Supplies" NODE 7 category in the slicer I want to list all the Medical Supply GL Accounts, their Voucher Amt, and the % of the overall Voucher spend on Medical Supply accounts. So I would like to get:
Account | Voucher Amt | Voucher Spend as Pct |
Antiseptics | $1,500 | 37.5% |
Catheters | $1,000 | 25% |
Oxygen | $1,500 | 37.5% |
Total | $4,000 | 100% |
<tbody>
</tbody>
I have tried changing the ALLSELECTED ( ) portion of the CALCULATE statement to do the 'Account Tree'[Account], 'Purchase Orders'[Account], and 'Voucher'[Account] but in all cases each row returns 100% so I'm obviously not understanding the correct use.
Thanks for your help.