Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sum a multiplication with if

Hi everyone, 

 

I am a newbie at Power BI, very used to Qlik but have to change software. 

I have an issue with a formula of that type : sum(quantity*if(cost_a<>0;cost_a;cost_b)). To do that, I have 2 tables :

- TRANSACTION

- ARTICLES

as shown below

I highlighted the column needed for the calculation. I then want to have the result of course for each product but also for each type of client (as you can see, the column is sadly empty) :

I tried to combine the IF function with the SUM, the SUM with FILTER, and didn't succeed to anything. I guess I don't know how to use the IF function properly. If anyone could help me on that issue, it would be great !

 

Thank you very much for your help, 

 

Delphine

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous Try like. maxx

     sumx(SUMMARIZE(ARTICLES;ARTICLES[ART_code];"M1";maxx(ARTICLES;if('ARTICLES'[ART_PRI]<>0; 'ARTICLES'[ART_PRI];'ARTICLES'[ART_PRI_Exact]));
 "M2";sum('TRANSACTION'[V_qte]));[M1]*[M2])

     

11 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous ,

    Try like: sumx(Summmarize(Article[ArtCOde],"M1",maxx(Atricle,if(Art_pri<>0, art_prc,art_pri_excat)),"M2",sum(Trascation[]V_qty)),[M1]*[M2])

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak 

       

       

      sumx(SUMMARIZE(ARTICLES;ARTICLES[ART_code];"M1";max(ARTICLES;if('ARTICLES'[ART_PRI]<>0; 'ARTICLES'[ART_PRI];'ARTICLES'[ART_PRI_Exact]));"M2";sum('TRANSACTION'[V_qte]));[M1]*[M2])but I have 2 errors :

       

      - cannot find one unique value for ART_PRI_Exact in talbe ARTICLES
      - cannot find the name ART_PRI and ART_PRI_Exact (fyi, Exact is the name of our ERP) 
      thank you for your help !
      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        Anonymous Try like. maxx

         sumx(SUMMARIZE(ARTICLES;ARTICLES[ART_code];"M1";maxx(ARTICLES;if('ARTICLES'[ART_PRI]<>0; 'ARTICLES'[ART_PRI];'ARTICLES'[ART_PRI_Exact]));
 "M2";sum('TRANSACTION'[V_qte]));[M1]*[M2])

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 

     

    Try combine with your actual model, I am not clear of how is your model looks like but try something like this:

     

     

    Measure = 
var costtable = SUMMARIZE('Transaction','Transaction'[ART_code], 'Transaction'[Product], 'Transaction'[Client Type],'Transaction'[Cost a], 'Transaction'[Cost b],"Cost", IF('Transaction'[Cost a]<>0,'Transaction'[Cost a],'Transaction'[Cost b]))

Return SUMX(ALLEXCEPT(costtable,[product],[client type]),'Transaction'[Quantity]*[Cost])

     

    The idea is create a column of actual cost, you can store it in the measure like above using Summarize, or just create a calculated column in the transaction table: 

     

    cost column= IF('Transaction'[Cost a]<>0,'Transaction'[Cost a],'Transaction'[Cost b])

measure = SUMX(ALLEXCEPT('Transaction',[product],[client type]),[Quantity]*cost column)

     

     

    Paul Zheng
    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
      6 years ago

      Hi Anonymous , 

       

      thank you for your answer, I get your idea, but why should I need to create a new table to be able to perform that formula or create a new column in the original table ? I think it complexifies the model by repeating a piece of information already existing. in Qlik, the formula is as follow : 

      sum({$<V_annee={'$(=max(CalendarYear))'},V_type={'*vente*'}>}V_qte*if(ART_PRI<>0,ART_PRI,ART_PRI_Exact))

      without any addition to the tables I have nore any new table (even temporary).

      If I have no other choice, I will create a new column in my table ARTICLES, but I rather won't.

       

      Thank you very much for your help !