trying to take an excel table into powerBI

[Nash2Bos]

Hello all, I have a table that looks like this

CITY	ITEM	Plan/Actual	Week 1	Week 2	Week 3	Week 4
Boston	Product	Plan	10	5	8	10
	Product	Actual	10	4	8	9
	Service	Plan	10	4	8	9
	Service	Actual	9	8	8	4
	Total	Plan	20	9	16	19
		Actual	19	12	16	13
		Plus/Minus	-1	+3	0	-6

The issue I am having is the total isnt a total of the column it is product.plan + service.plan and product.actual+service.actual. Also the plus minus in excel it is easy to to a formula for cells but not sure how to do it in PowerBI. 

I also have more cities this is just one city example. 

Thank you so much in advance for everyones help

[Anonymous · Accepted solution]

Hi @Nash2Bos ,

Organize the data so that all rows of Total are at the bottom of all cities, and then all rows of Total have all values of 0.

Select the CITY,ITEM,Plan/Actual columns and unpivot other columns. Then click the Close & Apply button to load the data into Power BI.

Use the following measure as the value.

Total = 
VAR _item = SELECTEDVALUE('Table'[ITEM])
VAR _plan = SELECTEDVALUE('Table'[Plan/Actual])
VAR _value = SUM('Table'[Value])
VAR _TotalActualValue = CALCULATE(SUM('Table'[Value]),'Table'[ITEM] IN {"Product","Service"} && 'Table'[Plan/Actual]="Actual"&&'Table'[CITY] <>BLANK())
VAR _TotalPlanValue = CALCULATE(SUM('Table'[Value]),'Table'[ITEM] IN {"Product","Service"} && 'Table'[Plan/Actual]="Plan"&&'Table'[CITY] <>BLANK())
RETURN
IF(_item IN {"Product","Service"},_value,
    SWITCH(_plan,
            "Actual", _TotalActualValue,
            "Plan", _TotalPlanValue,
            "Plus/Minus", _TotalActualValue - _TotalPlanValue
    )
)

Create a matrix using fields and the measure. 

For multiple cities.

The source data is as follows.

The final result is as follows. Hopefully it will meet your needs.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nash2Bos]

Thank you! 
quick question what if I want to do a plus/minus by city in between and then a final plus/minus at the bottom?