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 ha...
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])

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

amitchandak I got it : I am multiplicating a reference with a price... 😥 Let me try to fix it, and I tell you.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi amitchandak , I tried to do it step by step to understand where could the errors come from and it still doesn't work : even the IF function returns me an error "cannot find name" while I followed the example given there if function dax microsoft docs 

if([ART_PRI]<>0;[ART_PRI];[ART_PRI_Exact])

and "impossible to have one unique value"

if(ARTICLES[ART_PRI]<>0;ARTICLES[ART_PRI];ARTICLES[ART_PRI_Exact])

or 

if('ARTICLES'[ART_PRI]<>0;'ARTICLES'[ART_PRI];'ARTICLES'[ART_PRI_Exact])

(I still don't understand why sometimes you have ' before and after the name of the table, and sometimes you don't).

The COG calculation is supposed to be one of my simplest calculations, I am quite afraid for the other ones I have to do 😢

 

thank you very much for your help !

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, 

     

    I don't know why, but after a dozen tries, the IF function did work. So here is the formula now :

    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]));[M2]*[M1])

    it is a half success.

    When I put the measure in front of my articles, I got the expected result (I created a calculated column ART_PRI_calc to check the result) :

     

    but put in front of my client categories, it goes wrong :

    any idea ?

    thank you very much !

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, 

      I think I have a clue: Power BI didn't load my entire table ARTICLES, only half the lines (1300 vs 2500)... I am trying to understand why, because the table is not so big and my table TRANSACTION has been fully loaded while way to big. I deleted and recreated the query numerous times, still doesn't work. Is there a maximum of lines for xlsx files to be imported to Power BI ?

      thank you !

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        amitchandak I finally suceeded to load my whole table ARTICLES and your formula did work ! 😁 thank you very much ! I still have to resolve the not-loading issue (I deleted and recreated the query 5 times to make it work, without changing anything, so quite strange for me)