Forum Discussion

Geoffrey's avatar
Geoffrey
Frequent Visitor
8 years ago

Sum With Multiple filters

Hello Everyone,

 

I'm newbie in power BI environment and I don't find the answer to my question in all topics I found, I hope I didn't miss one!

 

I have several tables witch are gathering all my data of turnover per month. each table are named 2017_01_turnover, 2017_02_turnover, 2017_03....

 

I want to calculate the results of my full turnover for the whole year with several filters.

 

[Code_Client] : is the column of my customers' key which I want to allocate the turnover

Reseau : The tab where I want to gather result per Customer

Reseau[DC_X]: is one of the buyer's key allocated to customer

2017_01_Turnover'[DC_1]: The key allocated for each sell

 

I want to sum the turnover of each customer, which have the same buyer key (among the 6 they have) than each sell which are in turnover tables.

 

to sumup : =Client_Code && Reseau[DC] = '2017_01_Turnover'[DC_1]


Here the first code I created only for the first tab (2017_01_turnover, there is 11 others tab):

 

 

Ca_Net_01 = 
CALCULATE (
    SUM ( '2017_01_Turnover'[CA_Net]);
    FILTER (
        '2017_01_Turnover';
        '2017_01_Turnover'[Code_Client] =Reseau[Code_client]
        &&Reseau[DC_1]='2017_01_Turnover'[DC_1] 
        || Reseau[DC_2]='2017_01_Turnover'[DC_1]
        || Reseau[DC_3]='2017_01_Turnover'[DC_1] 
        || Reseau[DC_4]='2017_01_Turnover'[DC_1] 
        || Reseau[DC_5]='2017_01_Turnover'[DC_1] 
        || Reseau[DC_6]='2017_01_Turnover'[DC_1]
    )
)

I hope it's clear.

 

 

Thanks a lot!

Need Help

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi Geoffrey,

     

    Please refer to this blog to provide more detailed information so that I can understand your scenario more clear.

     

    Regards,

    Yuliana Gu

  • Geoffrey's avatar
    Geoffrey
    Frequent Visitor
    8 years ago

    I want to calculate per Code_Client the total of sales, but only sales of Code_client associate to Digit Code (DC_X, each Code_client can have until 31 Digit code)

     

    Dictionare

    Code_clientDC_1DC_2DC_3DC_4DC_5DC_6DC_7DC_8DC_9
    1C1347YDKP
    2TWMHEX953
    4SWP987   
    5SWP987   
    6SWP987   
    3SWP987   
    10SWP987   
    11SWP987   
    1520SWP987   
    656152SWP987   
    654564SWP987   
    6236SWP987   
    365462WMHEX953

     

    Verbatim

    Code_ClientPriceDC_1
    1113,559
    34,358
    65546,357
    61117,28S
    1110,79W
    1152,42X
    254457,91Z
    654518,060
    15198,81
    15203,84C

     

    What I am expecting are results like:

     

    Code_clientTotal
    1155,2
    2245,7
    152010,54
    65615280,96
    6545642546,78
    6236758,6
    365467548,55

     

    I hope, it's better like this

     

    Here my current code :

     

    Ca_Net_01 = 
CALCULATE (
    SUM ( '2017_01_Turnover'[CA_Net]);
    FILTER (
        '2017_01_Turnover';
        and('2017_01_Turnover'[Code_Client] = Reseau[Code_client] ; Reseau_DC[Code_Client] = Reseau[Code_client])
        && (
           Reseau_DC[DC_1]='2017_01_Turnover'[DC_1] 
        || Reseau_DC[DC_2]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_3]='2017_01_Turnover'[DC_1] 
        || Reseau_DC[DC_4]='2017_01_Turnover'[DC_1] 
        || Reseau_DC[DC_5]='2017_01_Turnover'[DC_1] 
        || Reseau_DC[DC_6]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_7]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_8]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_9]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_10]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_11]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_12]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_13]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_13]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_15]='2017_01_Turnover'[DC_1] 
        || Reseau_DC[DC_16]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_17]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_18]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_19]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_20]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_21]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_22]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_23]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_24]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_25]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_26]='2017_01_Turnover'[DC_1] 
        || Reseau_DC[DC_27]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_28]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_29]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_30]='2017_01_Turnover'[DC_1]
        || Reseau_DC[DC_31]='2017_01_Turnover'[DC_1]
        )
    )
)

    Cheers,

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      8 years ago

      Hi Geoffrey,

       

      Still have some concern:

      1. Are '2017_01_Turnover' and 'Reseau' represent Dictionare and Verbatim in your provided sample data?
      2. But where is field '2017_01_Turnover'[CA_Net]? And what is table 'Reseau_DC'?
      3. In above expected result table, how to calculate the value of Total? Please illustrate with examples?
      4. I still cannot understand the intention of above DAX formula.

       

      One suggestion you can have a try is unpivoting 'Dictionare' table first.

       

      Regards,

      Yuliana Gu