cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bhaveshp Member
Member

How to write DAX statement using where condition

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.

 

Capture.PNG

 

 

Any help will be appreciated, thank you 

 

1 ACCEPTED SOLUTION

Accepted Solutions
yash1105 Member
Member

Re: How to write DAX statement using where condition

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)

 

4 REPLIES 4
yash1105 Member
Member

Re: How to write DAX statement using where condition

Have you tried "Percentage of Grandtotal" ?

percental grandtotal.png

Bhaveshp Member
Member

Re: How to write DAX statement using where condition

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

 

Capture.PNG

 

 

yash1105 Member
Member

Re: How to write DAX statement using where condition

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)

 

Community Support Team
Community Support Team

Re: How to write DAX statement using where condition

Hi @Bhaveshp

Assume your dataset is like this

3.png

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

4.png

 

Best Regards

Maggie

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 270 members 2,728 guests
Please welcome our newest community members: