Adding a calculated colum with different aggregated level

[jostnachs]

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 : 

TotalEstimatedCommissionByMainAccount_Column =

CALCULATE(

    SUM(V_DimPolicy[EstimatedCommission]),

    FILTER(

        ALL(V_DimClient),

        V_DimClient[MainAccount]=RELATED(V_DimClient[MainAccount]))

)

measure: 

TotalEstimatedCommissionByMainAccount_Measure =

CALCULATE(

    SUM(V_DimPolicy[EstimatedCommission]),

    ALLEXCEPT(V_DimClient,V_DimClient[MainAccount])

)

[ryan_mayu]

could youpls provide some sample data and expected output?

[jostnachs]

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!

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the expected result very clearly.

[jostnachs]

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

[ryan_mayu]

you can try this

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

[jostnachs · Accepted solution]

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

[jostnachs]

this works as a measure.... but i want it as a calculated column.

[jostnachs]

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

[jostnachs]

Any help Please @ryan_mayu 

[jostnachs]

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.