Matrix is not calculating due to values from separate rows

[MarkusEng1998]

I want to compare the areas between two rooms, Area 1 and Area 2.

The challenge is that these two areas are in separate rows because the names vary and come from separate data sources.

In the PowerBI matrix i create a lookup value to group the two areas into one name.

I am able to calculate the difference between the two areas, but am failing on getting the % right.

The Excel shows how I would like the data reported. the Power BI shows how far I've gotten.

1) Area 1 and Area 2 are pulled from separate datasources (Excel worksheets).

2) PowerBI merges the two datasources.

3) create a pivot on the area designe area

4) link a lookup table to normalize the room names.

5) output the data in a matrix with normalized room names as the row, and area values and calculations as values. i.e. no columns in the pivot.

Please advise.

Thank you in advance.

[v-angzheng-msft]

Hi, @MarkusEng1998 

What you need to do now is get the correct percentage right?

To add a custom column in powerQuery as follows.

Then do a  unpivot on the columns to create a matrix visual.

result:

Please refer to the attachment below for details. Hope this helps.

If I did not understand your question correctly, please consdier sharing more details about it and drawing a simple picture to show your expected visual or a simple sample file without any sesentive information for further discussion.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarkusEng1998]

Thank you Vahid for taking a look. here is a sample dataset

[PaulDBrown]

Please follow the recommedations outlined in this thread:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 

[VahidDM]

Hi @MarkusEng1998 

It would be great if you share a sample of your data in the table format here.

I'm not sure, but try this measure:

EU GAP =
VAR _EUAMZL =
    MAX ( 'table'[EU AMZL] )
VAR _NAAMZL =
    MAX ( 'table'[Na AMZL] )
RETURN
    IF ( _EUAMZL < _NAAMZL, _NAAMZL / _EUAMZL, _EUAMZL / _NAAMZL )

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

Appreciate your Kudos  !!