Can I blend IF and SUMX?

[jima]

I am struggling to manipulate some data, many "experts" in our business have tried but failed.

I want to present a matrix of data to show revenue and margin (%) for current YTD and  last Year to data. I want to drill into the varioud attributes to spot what the levers are.

My problem comes from the data coming in a single table, I get both profit YTD (amount) and revenue YTD figures (out of a busines objects service) in this single table, so revenue and profit both show as rows in the datatable. There are no reference fields to link row together other than the column attributes.

The source table looks like this, there are other fields on top of this, nothing unique but this should give the flavour, each row is aways either rev or margin with the other attributes.

Item	Country	Group	Sector	Actual YTD	Last Year YTD
Meat Revenue	USA	1	Consumer	1,000	900
Dairy Revenue	GB	3	Business	900	1,100
Finance Revenue	USA	2	Consumer	450	425
Hardware Revenue	FR	3	Consumer	125	180
Meat Profit	USA	1	Consumer	350	300
Dairy Profit	GB	3	Business	450	500
Finance Profit	USA	2	Consumer	150	150
Hardware Profit	FR	3	Consumer	30	50
Finance Profit	FR	2	Business	300	310
Hardware Profit	USA	2	Business	600	600
Finance Revenue	FR	2	Business	925	1200
Hardware Revenue	USA	2	Business	2400	2500
Meat Profit	GB	2	Consumer	120	130
Dairy Profit	USA	1	Consumer	150	160
Meat Revenue	GB	2	Consumer	250	260
Dairy Revenue	USA	1	Consumer	750	725

I want to be able to show this.....

	Actual YTD	Last Year YTD	Actual YTD	Last Year YTD
Meat	1,250	1,160	37.6%	37.1%
Dairy	1,650	1,825	36.4%	36.2%
Finance	1,375	1,625	32.7%	27.7%
Hardware	2,525	2,680	25.0%	23.5%
TOTAL	6,800	7,290	31.6%	29.8%

On the original data, I managed to create measures to calculate the profit percentages but I can only do a measure each for meat, dairy, finance or hardware. I can't then combine the measures in one table.

Can I make an if statement that has the sumx as an expression, so one of the row columns eg Dairy then determines the result in the corresponding columns of the same row?

Help appreciated, nobobdy so far has found a way around this.....

Thanks in advance

Jim

PS an obvious one is to change the whole data extract from the BO service, I want to see if it is doable without going down that road.

[camargos88 · Accepted solution]

Hi @jima ,

I've created this file as an example: Download PBIX 

I hope it helps you.

Ricardo

[jima]

Hi Ricardo,

that will do the trick nicely, i can see now what you did, just came at it from a different (the better) direction. I need to do someting with one of the columns to work some other types but your solution works nicely, thanks very much.

[camargos88]

Hi @jima ,

I'm glad it worked.

Note that the key point is to split the column and work as a separated column, I splitted the column using Power Query.

Ricardo