Forum Discussion
Adding a calculated colum with different aggregated level
I have Mainaccount and clientname, each mainaccount has multiple clientnames and the mainaccount is client by itself in my client dimensiontable. I have commission in fact policy table. the commission is per each client as shown in screenshot one. but i want to add a new column in policy table where i have to calculate the commission at mainaccount level not at clientlevel. but i am unable to acheive it using allexcept. I am able to achive it as a measure as shown in screenshot2 but i am unable to acheive it as a column. even after using allexcept it doesnt erase the clientname filter.
Below is the dax i have written to add column and below that is the result but i want 145980 to show up not at clientname level
Column :
measure:
figured this out... I am just using wrong table in allexcept... thanks all for the help!
10 Replies
could youpls provide some sample data and expected output?
Like i have heirarchy like mainaccount and clientname in one table and have estimated commission in another table. estimated commission is calculated at clientname level. I want to add a column in my fact table calculating estimated commission at mainaccount level not at client level.
I have Clientname column (dimclienttable), mainaccount column (dimclienttable) and estimatedcommssion column (policytable).dimclienttable and policytable have one-many relationship. I want to add a column in policytable calculated as estimatedcommission at mainaccount level not at client level(one mainaccount has multiple clientnames and the
mainaccount can be client by itself). date is shown below.
ClientName MainAccount IsMain Sum of EstimatedCommission 10 Federal Holdings LLC 10 Federal Holdings LLC TRUE $94,163 MV at Boone LLC 10 Federal Holdings LLC FALSE $50,769 Davinci Lock Self Storage, Inc. 10 Federal Holdings LLC FALSE $939 Bowman RD 1, LLC 10 Federal Holdings LLC FALSE $109 10 Federal Finance LLC 10 Federal Holdings LLC FALSE $0 10 Federal Sitework LLC 10 Federal Holdings LLC FALSE $0 10FSS 1453 Fernwood Glendale Rd Spartan 10 Federal Holdings LLC FALSE $0 10FSS 2601 Industrial Dr. 10 Federal Holdings LLC FALSE $0 $145,980
But what is want is an extra column where sumofestimatedcommission summed up to mainaccount level as below:
ClientName MainAccount IsMain Sum of EstimatedCommission Sum of TotalEstimatedCommissionByMainAccount 10 Federal Holdings LLC 10 Federal Holdings LLC TRUE $94,163 $145,980 MV at Boone LLC 10 Federal Holdings LLC FALSE $50,769 $145,980 Davinci Lock Self Storage, Inc. 10 Federal Holdings LLC FALSE $939 $145,980 Bowman RD 1, LLC 10 Federal Holdings LLC FALSE $109 $145,980 10 Federal Finance LLC 10 Federal Holdings LLC FALSE $0 $145,980 10 Federal Sitework LLC 10 Federal Holdings LLC FALSE $0 $145,980 10FSS 1453 Fernwood Glendale Rd Spartan 10 Federal Holdings LLC FALSE $0 $145,980 10FSS 2601 Industrial Dr. 10 Federal Holdings LLC FALSE $0 $145,980 I am using the below formula:
TotalEstimatedCommissionByMainAccount =CALCULATE(SUM(V_DimPolicy[EstimatedCommission]),FILTER(ALL(V_DimClient),V_DimClient[MainAccount]=RELATED(V_DimClient[MainAccount])))but it is not giving me required result like i showed in above table. instead it still gives me aggregated at clietname level as shown below:ClientName MainAccount IsMain Sum of EstimatedCommission Sum of TotalEstimatedCommissionByMainAccount 10 Federal Holdings LLC 10 Federal Holdings LLC TRUE $94,163 $94,163.30 MV at Boone LLC 10 Federal Holdings LLC FALSE $50,769 $50,769.30 Davinci Lock Self Storage, Inc. 10 Federal Holdings LLC FALSE $939 $939.45 Bowman RD 1, LLC 10 Federal Holdings LLC FALSE $109 $108.75 10 Federal Finance LLC 10 Federal Holdings LLC FALSE $0 $0 10 Federal Sitework LLC 10 Federal Holdings LLC FALSE $0 $0 10FSS 1453 Fernwood Glendale Rd Spartan 10 Federal Holdings LLC FALSE $0 $0 10FSS 2601 Industrial Dr. 10 Federal Holdings LLC FALSE $0 $0 Please help!
you can try this
=calculate(sum(EstimatedCommission),allexcept(table, MainAccount))
