Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Row context Filter

I need help calculating Gross Margin from Between Budget & Actual Based on my specific row on my Column. Here is my query 

Gross Margin = IF(
HASONEFILTER(Finance[Line Description]),
DIVIDE([Actual Values]-[Budget Values],[Budget Values]))

 

Here is the Specific rows..  
Baked Goods Gross Margin (31290)                           % 
Baked Goods Gross Margin (31290)                          %
Cold Disp Beverage Gross Margin (31294)                %
Cold Grab & Go Gross Margin (37969)                       %
Frozen Disp Beverage Gross Margin (31295)             %
FS Prep on Site Gross Margin (31293)
Hot Disp Beverage Gross Margin (31296)
Hot Foods Gross Margin (37968)
Other Food Service Gross Margin (31292)
Other Retail Food Service Gross Margin (37970)
QSR Gross Margin (31297)
Baked Goods Gross Margin (31290)
Baked Goods Gross Margin (31290)
Cold Disp Beverage Gross Margin (31294)
Cold Grab & Go Gross Margin (37969)
Frozen Disp Beverage Gross Margin (31295)
FS Prep on Site Gross Margin (31293)
Hot Disp Beverage Gross Margin (31296)
Hot Foods Gross Margin (37968)
Other Food Service Gross Margin (31292)
Other Retail Food Service Gross Margin (37970)
QSR Gross Margin (31297)
Roller Grill Gross Margin (31291)

13 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 

    Create 'Specific Rows' Table or import it from it from excel. Then create a measure 

    Filter Measure =
    COUNTROWS ( FILTER ( Finance, Finance[Line Description] IN 'Specific Rows'[ Specific Rows]) )
    Place this measure in the filter pane of the table visual and select "Is not blank" and apply the filter. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      tamerj1  Please read my post. I want Budget/Actual. but, I want the % to be on specific row.. which i posted. They are other row but, i want % to be on specific row while other rows remain the same.

      Here are the specific i wanted the % on. 

      Baked Goods Gross Margin (31290)                           % 
      Baked Goods Gross Margin (31290)                          %
      Cold Disp Beverage Gross Margin (31294)                %
      Cold Grab & Go Gross Margin (37969)                       %
      Frozen Disp Beverage Gross Margin (31295)             %
      FS Prep on Site Gross Margin (31293)
      Hot Disp Beverage Gross Margin (31296)
      Hot Foods Gross Margin (37968)
      Other Food Service Gross Margin (31292)
      Other Retail Food Service Gross Margin (37970)
      QSR Gross Margin (31297)
      Baked Goods Gross Margin (31290)
      Baked Goods Gross Margin (31290)
      Cold Disp Beverage Gross Margin (31294)
      Cold Grab & Go Gross Margin (37969)
      Frozen Disp Beverage Gross Margin (31295)
      FS Prep on Site Gross Margin (31293)
      Hot Disp Beverage Gross Margin (31296)
      Hot Foods Gross Margin (37968)
      Other Food Service Gross Margin (31292)
      Other Retail Food Service Gross Margin (37970)
      QSR Gross Margin (31297)

      Roller Grill Gross Margin (31291)

       

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      Anonymous 

      Some like this

      IF (

      ISEMPTY (FILTER ( Finance, Finance[Line Description] IN 'Specific Rows'[ Specific Rows]) ),

      "",

      [measure]
      )

      however, you might recieve error

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        tamerj1 Thanks, i will try it. But, i tried something like below it won't display anything .


         

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi  Anonymous 

    I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

    If you need more help, please let me know.

     

    Best Regards,

    Community Support Team _Tang

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