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
Daniel29195's avatar
Daniel29195
Icon for Community Champion rankCommunity Champion
2 years ago

Hello NadeemAhamed 

 

kindly check now : filename : Calucating Variance, Achievement from Plan and Actual1

https://drive.google.com/file/d/1Fh9SEHvOatffQFsUoCBmVciNK7NCk9jw/view?usp=sharing

 

 

this is the data used : 

 

this is the output : 

 

 

 

 

 

 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

 

 

 

 

 

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

Daniel29195 

 

Thank you soo much for your solution,

But i found some calucation is wrong. 

Gross Profit Rate is getting wrong calculation in all columns (Plan,Actual, Variance & Achievement). Other Values are coming correct. 

 

 

Could you kindly help me out to resolve this.

  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    NadeemAhamed 

    the formula states : 

    grossprofit /  sales 

    thus :

    9/36 =  25 %

    44/10 =  440 %

    35 / -26 =  -134%

     

    unless the logic is something else

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

    Daniel29195 

     

    For Plan and Actual column data is correct- Gross Profit Rate = Gross Profit/Sales.

    Variance and Acheivement column for "Gross Profit Rate" values should be like other values in the column .

    Variance = 440.0% - 25.00%=415%

    Achievement = 440.0% / 25.00% = 17.6%

  • 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]
    )