Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Excel Formula conversion to DAX

I have a average calculation base for % perfomance, and can get the number by doing multiple steps within Power BI measure, However hoping someone can point me in the correct direction to express the Formula in 1 step. 

 

This is the full below formula, I was able to to it breaking up in to steps to get each answer then combine, however hoping some once can point me in the correct direction to make this into 1 specific formula converted in DAX. 

Each X and Y are columns related to specific information on each line in Power Bi. 

 

=Y1*((1-X2)*(X3/(X3+X4))+(1-Y2)*(X4/(X3+X4)))/(1-Y3)

 

Thank you 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Such things should be calculated in Power Query, mate, not in DAX. For many reasons but one of them is that calculated columns are worse compressed than regular columns.

    By the way, please try to learn the difference between measures and calculated columns.

    Best
    D
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Please share sample data and expected output.
    Thanks
    Pravin
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Please see below Data 

       

       

       
       
       

      Formula in Excel is CR=Y1*((1-WA%)*(Prod C/(Prod C+Prod P))+(1-WP%)*(Prod P/(Prod C+Prod P)))/(1-%_F)

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Create new column in power BI as below

         

        CR=table[Y1]*((1-table[WA%])*(table[Prod C]/(table[Prod C]+table[Prod P]))+(1-table[WP%])*(table[Prod P]/(table[Prod C]+table[Prod P])))/(1-table[%_F])

         

        I assume here all values are numeric not of type text.

        and all are column not measure.

         

        Note: replace "Table" with your table name in above formula.

         

        Thanks & regards,
        Pravin Wattamwar
        www.linkedin.com/in/pravin-p-wattamwar

        If I resolve your problem Mark it as a solution and give kudos.