Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

aggregation measure

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

Need Help

3 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-xicai
        Community Support
        7 years ago

        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