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

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

    Hi amitchandak 

    thank you for your quick answer ! ðŸ™‚ I had an error first with MAXX that's why I put only MAXX (did not understand there were 2 different functions, sorry). I have no more error on the measure but it can't be shown on the screen : there is another error "la fin de l'entrée a été atteinte" which could be translated by "the end of the entry has been reached". Any idea ?

    thank you very much, 

    Delphine

    • 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 !