How to write DAX statement using where condition

[Bhaveshp]

Here is the visual of power BI report shown below for summary table.

The Actual, budget values are summing up for Sales, Cost of Sales, Gross Profit in % of Sales. 

But I am looking for an average of values for Report Parameter "Gross Profit in % of Sales". 

How to write the DAX formula for a new measured Value (which summarizes Sales, Cost of Sales, and averages Gross profit in % of Sales) 

PS: The Report parameter is varchar type, Sales are in decimal type.

Any help will be appreciated, thank you 

[v-juanli-msft]

Hi @Bhaveshp

Assume your dataset is like this

Then create a new measure to calculate an average of values for Report Parameter "Gross Profit in % of Sales".

Measure 2 = IF(MAX([cater])="Gross profit in % of sales",AVERAGEX(ALL(Sheet1),[Measure]),[Measure])

Best Regards

Maggie

[Anonymous]

Have you tried "Percentage of Grandtotal" ?

[Bhaveshp]

@Anonymous, Yes when I try using show value as percentage of grand total, the other parameters (Sales, Cost of Sales) are changing as well. 

[Anonymous · Accepted solution]

Looks like your column in Level 2 has 3 values, and you are using just 1 measure.

You cannot change the measure properties of 1 of the row values. (To show 2  rows as sum and one row as percentage)