Forum Discussion

NadeemAhamed's avatar
NadeemAhamed
Icon for Helper V rankHelper V
2 years ago

Row wise calculation in percentage

Dear Community.  Good Day.    I have posted this before, unfortunately i didn't get the answers. Requesting all to help me out.   I have one P&L report. in this i have received only Plan and Ac...
General Comment
Need Help
Show and Tell
Tips and Tricks
NadeemAhamed's avatar
NadeemAhamed
Icon for Helper V rankHelper V
2 years ago

Daniel29195 ,

 

what pbix file you have shared that is contain the old pbix data.

Is it that you have shared?

NadeemAhamed's avatar
NadeemAhamed
Icon for Helper V rankHelper V
2 years ago

Daniel29195 ,

 

Kindly let me know if any clearification and more details required.

 

  • NadeemAhamed's avatar
    NadeemAhamed
    Icon for Helper V rankHelper V
    2 years ago

    Dear Daniel29195 ,

     

    I tried this below DAX for above requirement. but bad luck.

    Could you please help me out to achieve this 

     

     

    test_Variance data =

    var p_sales =
    CALCULATE(
        SUM('P&L Report'[Plan]),
        Category[Category] = "Sales",REMOVEFILTERS(Category[Order])
    )

    var p_grossprofit =  
    CALCULATE(
        SUM('P&L Report'[Plan]),
        Category[Category] = "Gross Profit",REMOVEFILTERS(Category[Order])
    )

    var p_Grossprofitrate=
    DIVIDE(
        p_grossprofit,
        p_sales,
        0
    )


    var a_sales=
    CALCULATE(
        SUM('P&L Report'[Actual]),
        Category[Category] = "Sales",REMOVEFILTERS(Category[Order])
    )
    var a_grossprofit=
    CALCULATE(
        SUM('P&L Report'[Actual]),
        Category[Category] = "Gross Profit",REMOVEFILTERS(Category[Order])
    )

    var a_Grossprofitrate =
        DIVIDE(
        a_grossprofit,
        a_sales,
        0
    )

    var gpr=
    CALCULATE( a_grossprofit-p_grossprofit)


    return
    SWITCH(
        TRUE(),
        SELECTEDVALUE(Category[Category]) = "Gross Profit Rate (Gross Profit/Sales)" ,  format(gpr, "##.#%"),
        [variance]
    )