Row context Filter

[Anonymous]

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)

[v-xiaotang]

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.

[Anonymous]

Yes, The issue was resolve by Tamer

[tamerj1 · Accepted solution]

Hi @v-xiaotang 

this was answered in another post 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dax-CALCULATION-ISSUE/m-p/2485927#M68256

[tamerj1]

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. 

[tamerj1]

@Anonymous 

Some like this

IF (

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

"",

[measure]
)

however, you might recieve error

[Anonymous]

@tamerj1  Your DAX works, But, i want the % to display only on the listed row not on other rows. right now it still display on other rows. 

[tamerj1]

Hi @Anonymous 

then use

IF (

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

[measure],

""
)

however, you might recieve error

[Anonymous]

@tamerj1  The Specific rows is part of the Line Description. Line Desc is the column name.

[tamerj1]

Yes. You should have them in a separate single column table. Of course you should use the real name of this column and its table. 

[Anonymous]

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

[tamerj1]

@Anonymous 

I'm a little lost here. Do you want to show the value for the other rows as blanks or you want to entirely hide the row? 

[Anonymous]

Yes!. I want to show the value of the other row as blank. Everything will be mapped to GM % only which i already have. Some like this. 

[Anonymous]

@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)