Forum Discussion

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

Avoid getting sum to Calcluate column values

Dear Daniel29195 Sir.

Good Day. 

 

I have created P&L report in Power BI.

This is my source data in excel sheet. 

 

I have attached the Pbix for your reference.

https://toyotsu.box.com/s/mboowbzdtokv925yklhig6t8dd74uli5

 

I have Division Dropdon when we want to show all data for Multiple Divisions such as Admin&IT or All

Plan and Actual values are getting sum.

Variance,Achievement and Gross Profit (Which are calucated values) are also getting sum.

But actually Variance, Achievement and Gross Profit values are should take from the sum of Plan and sum of Actual values when we select Multiple Divisions.

 

Thanks in Adavance. 

 

General Comment
Need Help
Show and Tell
Tips and Tricks

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    NadeemAhamed , Try like

     

    Gross Profit Rate =
    DIVIDE(
    calculate(SUM(Table[Actual]), Table[Category] = "Gross Profit"),
    calculate(SUM(Table[Actual]), Table[Category] = "Sales")
    )

     

    It works best when you category in dim table

     

    You can also try this hwen you have dim

    Gross Profit Rate =
    DIVIDE(
    calculate(SUM(Table[Actual]), filter( all(category) , category[Category] = "Gross Profit")),
    calculate(SUM(Table[Actual]), filter( all(category) , category[Category] = "Sales"))
    )

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

      Dear amitchandak ,

       

      Thank you for your valuable time to reading this post and replying me. 

       

      Here i need help to avoid the Variance and Achievement values to get sum of.

      Bcz i want to take the Variance and Achievement values from the current plan and actual values for all category when we are selecting the multiple division. 

      Its working fine when we are selecting the single division.