Syntax error in new Measure (SUMX & FILTER)

[Ceane]

Hi,  I'm new to PowerBI, but relatively profficient in excel. However no experience with DAX formulas.

I am creating a BI dashboard based on some financial data, and I'm trying to create a new measure using a combo of SUMX & FILTER functions, but clearly I don't understand the syntax properly. This is the situation.

I have a query that looks somthing like this:

Currency	Cost Type	Division	Location	Date	Amount
AUD	Total Employment Cost	Chemistry	Africa	1/01/2015	500
AUD	EBIT	Biology	Europe	1/01/2015	400
AUD	Net Revenue	Science	Asia	1/01/2015	300
AUD	TEC	Gardening	North America	1/01/2015	200
AUD	EBIT	Chemistry	South America	1/01/2015	500
USD	Net Revenue	Biology	Africa	1/01/2015	600
USD	TEC	Science	Europe	1/01/2015	200
USD	EBIT	Gardening	Asia	1/01/2015	100
etc	etc	etc	etc	etc	etc

Each location has an EBIT, TEC and Net Revenue Value, in USD & AUD.

I would like to create a new Measure (Cost Base) = Revenue - EBIT, and a second Measure (Other Costs) = Cost Base - TEC.

To do this I've tried:

Cost Base = sumx(filter('Cost Types','Cost Types'[Cost Type]=EBIT),'Cost Types'[Amount])  

But I keep getting errors.  I'm pretty sure they revolve around the "EBIT" but I'm not sure how to resolve. And I haven't even attempted the second part of the equation yet: do i do sumx - sumx?

The end goal is to create a stacked bar graph over time, with TEC:OtherCosts:EBIT where the total = revenue. This graph will be sliced by currency, division and location.

Is any of this possible?

[v-huizhn-msft · Accepted solution]

Hi @Ceane,

As the @Phil_Seamark, if the data type is text, you should use "EBIT" rather than EBIT. For your requriement of 'Cost Base = Revenue - EBIT', the Revenue is Net Revenue, right? If it is, please create measure using the following formulas.

Cost Base = sumx(filter('Cost Types','Cost Types'[Cost Type]="Net Revenue"),'Cost Types'[Amount])-sumx(filter('Cost Types','Cost Types'[Cost Type]="EBIT"),'Cost Types'[Amount])

Other Costs = 'Cost Types'[Cost Base]- sumx(filter('Cost Types','Cost Types'[Cost Type]="TEC"),'Cost Types'[Amount])

Then create three slicer including currency, division and location seperately. Create a  a stacked bar graph, select the date as axis level, the Cost and Other Costs measures are value level, you will get the chart like the following screenshot.

You can click the Format->Edit Interactions, edit if the slicer effect bar chart. When you hit button highlighted in yellow background, the slicer will effect the chart. Otherwise, the slicer doesn't effect chart when you select the circle(highlighted in red line).



If you have any other problem, please let me know.

Best Regards,
Angelia

[Phil_Seamark]

Give this a crack

Cost Base = sumx(filter('Cost Types','Cost Types'[Cost Type]="EBIT"),'Cost Types'[Amount])