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...
Need Help
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