Forum Discussion

nleuck's avatar
nleuck
Post Patron
7 years ago

DAX Formula

Hello All,

 

I'm currently trying to recreate a formula in Power BI that is being used in Excel for a report. On the image below I've place an M above the columns of my table that represent Measures I created. Sales Amount is a column that has a Default Summarization of Sum. I need a formula that looks like this:

 

(total of Prev Period Gross Margin $, subtract Prev Period Gross Margin $, add Gross Margin $)

 divide--------------------------------------------------------------------------------------------------

(total of Prev Period Sales Amount, subtract Prev Period Sales Amount, add Sales Amount)

 

(275,446 - 4915 + 300) / (1,138,962 - 50,060 + 2,409) -----> 270,831 / 1,061,311 ------> .24817 ------> 24.82%

 

Any help would be greatly appreciated!!!

 

Thanks,

 

Need Help

5 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    nleuck,

     

    Create a measure and try DAX formula like pattern below:

     

    Measure =
CALCULATE ( SUM ( Table[Prev Period Gross Margin] ), ALL ( Table ) )
    - MAX ( Table[Prev Period Gross Margin] )
    + MAX ( Table[Gross Margin] )
        / CALCULATE ( SUM ( Table[Prev Period Sales Amount] ), ALL ( Table ) )
    - MAX ( Table[Prev Period Sales Amount] )
    + MAX ( Table[Sales Amount] )

    Regards,

    Jimmy Tao

     

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        7 years ago

        nleuck,

         

        Could you share the original table for further test?

         

        Community Support Team _ Jimmy Tao

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.