Forum Discussion

jostnachs's avatar
jostnachs
Helper IV
1 year ago
Solved

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 commissio...
Need Help
  • jostnachs's avatar
    jostnachs
    1 year ago

    figured this out... I am just using wrong table in allexcept... thanks all for the help!

jostnachs's avatar
jostnachs
Helper IV
1 year ago

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.

ClientNameMainAccountIsMainSum of EstimatedCommission
10 Federal Holdings LLC10 Federal Holdings LLCTRUE$94,163
MV at Boone LLC10 Federal Holdings LLCFALSE$50,769
Davinci Lock Self Storage, Inc.10 Federal Holdings LLCFALSE$939
Bowman RD 1, LLC10 Federal Holdings LLCFALSE$109
10 Federal Finance LLC10 Federal Holdings LLCFALSE$0
10 Federal Sitework LLC10 Federal Holdings LLCFALSE$0
10FSS 1453 Fernwood Glendale Rd Spartan10 Federal Holdings LLCFALSE$0
10FSS 2601 Industrial Dr.10 Federal Holdings LLCFALSE$0
   

$145,980

 

But what is want is an extra column where sumofestimatedcommission summed up to mainaccount level as below:

ClientNameMainAccountIsMainSum of EstimatedCommissionSum of TotalEstimatedCommissionByMainAccount
10 Federal Holdings LLC10 Federal Holdings LLCTRUE$94,163$145,980
MV at Boone LLC10 Federal Holdings LLCFALSE$50,769$145,980
Davinci Lock Self Storage, Inc.10 Federal Holdings LLCFALSE$939$145,980
Bowman RD 1, LLC10 Federal Holdings LLCFALSE$109$145,980
10 Federal Finance LLC10 Federal Holdings LLCFALSE$0$145,980
10 Federal Sitework LLC10 Federal Holdings LLCFALSE$0$145,980
10FSS 1453 Fernwood Glendale Rd Spartan10 Federal Holdings LLCFALSE$0$145,980
10FSS 2601 Industrial Dr.10 Federal Holdings LLCFALSE$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:
ClientNameMainAccountIsMainSum of EstimatedCommissionSum of TotalEstimatedCommissionByMainAccount
10 Federal Holdings LLC10 Federal Holdings LLCTRUE$94,163$94,163.30
MV at Boone LLC10 Federal Holdings LLCFALSE$50,769$50,769.30
Davinci Lock Self Storage, Inc.10 Federal Holdings LLCFALSE$939$939.45
Bowman RD 1, LLC10 Federal Holdings LLCFALSE$109$108.75
10 Federal Finance LLC10 Federal Holdings LLCFALSE$0$0
10 Federal Sitework LLC10 Federal Holdings LLCFALSE$0$0
10FSS 1453 Fernwood Glendale Rd Spartan10 Federal Holdings LLCFALSE$0$0
10FSS 2601 Industrial Dr.10 Federal Holdings LLCFALSE$0$0

Please help!

ryan_mayu's avatar
ryan_mayu
Super User
1 year ago

you can try this

 

=calculate(sum(EstimatedCommission),allexcept(table, MainAccount))

  • jostnachs's avatar
    jostnachs
    Helper IV
    1 year ago

    Hi, Thanks for the suggestion. I tried the formula and still gives me same values. I dont know what i am doing wrong.

     

  • jostnachs's avatar
    jostnachs
    Helper IV
    1 year ago
    this works as a measure.... but i want it as a calculated column.
  • jostnachs's avatar
    jostnachs
    Helper IV
    1 year ago

    figured this out... I am just using wrong table in allexcept... thanks all for the help!