aggregation measure

[Anonymous]

Hi

I have a two tables one is FactSales and one is DimCust.

FactSales

Customer #	Season	Units Sold
123	S20	10
123	S20	5
234	S20	15
234	S19	20
345	S20	5
123	S19	10
345	S20	15

DimCust

Customer #	Customer Name	Account Mng #	Account Mng Name
123	Store A	987	Johny Smith
234	Store B	999	Peter Lee
345	Store C	987	Johny Smith

The relationship is built on the Customer #. I would like to calculate Sale per Account Mng in the S20 Season

I was trying something like

SalePerAcctMng =   

Var sum = CALCULATE(SUM(‘FactSales’[Units Sold]),’FactSales’[Season]=”S20”)

Return

CALCULATE(x,VALUES(‘DimCust’[Account Mng #]

But it doesn’t really work the way I want. When I put the measure in a table it will show something like this when I put it in the table

Acct Mng Name	Customer Name	Units Sold	SalePerAcctMng
Johny Smith	Store A	25	35
Johny Smith	Store C	20	35

Can someone help? Thanx

[v-xicai]

Hi @Anonymous ,

You may try to create measure like DAX below.

SalePerAcctMng = CALCULATE(SUM(FactSales[Units Sold]),FILTER(ALLSELECTED(FactSales),FactSales[Season]="S20"&&FactSales[Customer #]=MAX(DimCust[Customer #])))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xicai 

Thanx for quick response but it it not what i need. It gives me the max but what i need is to have the sum of all customer per that Account Manager. ... the number in the SalePerAcctMng should be same numbers for the same Account manager . 

So I need the Sum of Units sold in S20 to iterate over the Table of Account Managers 

[v-xicai]

Hi @Anonymous ,

I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy