How to create a matrix with one table column as matrix row and column header?

[arsalaan]

I am trying to compare conversion rates when exchanging from one country to another. I have column Country as the rows and columns of my Matrix as shown below, and I am trying to populate it with a measure which is the percentage difference in coversion accross countries. 

My measure = 

=

var index1 = LOOKUPVALUE(LOCATION_ADJUSTMENT_CON[Index],LOCATION_ADJUSTMENT_CON[Country],SELECTEDVALUE(LOCATION_ADJUSTMENT_CON[Country]))

var index2 = LOOKUPVALUE(LOCATION_ADJUSTMENT_CON[Index],LOCATION_ADJUSTMENT_CON[Country], SELECTEDVALUE(LOCATION_ADJUSTMENT_CON[Country]))

return

index1/index2

The above does not work, what am I doing wrong. My desired output is as shown below:

[Anonymous]

HI @arsalaan,

AFIAK, current power bi does not support to get different values from the same source table field at the same time.

In my opinion, I'd like to suggest you create a new unconnected table with all country values, then you can use this field with raw table country as row and column to design the matrix.

NewTable =
DISTINCT ( ALL ( LOCATION_ADJUSTMENT_CON[Country] ) )

measure formula =
VAR currCountry =
    VALUES ( LOCATION_ADJUSTMENT_CON[Country] )
VAR newCountry =
    VALUES ( NewTable[Country] )
VAR value1 =
    CALCULATE (
        SUM ( LOCATION_ADJUSTMENT_CON[Index] ),
        FILTER (
            ALLSELECTED ( LOCATION_ADJUSTMENT_CON ),
            LOCATION_ADJUSTMENT_CON[Country] IN currCountry
        )
    )
VAR value2 =
    CALCULATE (
        SUM ( LOCATION_ADJUSTMENT_CON[Index] ),
        FILTER (
            ALLSELECTED ( LOCATION_ADJUSTMENT_CON ),
            LOCATION_ADJUSTMENT_CON[Country] IN newCountry
        )
    )
RETURN
    value1 / value2

Regards,

Xiaoxin Sheng

[arsalaan]

Hey, do you think you can help me out? Really frustrating problem

[Anonymous · Accepted solution]

HI @arsalaan,

You can try to modify the allselected to all to ignore the filter effects:

measure formula =
VAR currCountry =
    VALUES ( LOCATION_ADJUSTMENT_CON[Country] )
VAR newCountry =
    VALUES ( NewTable[Country] )
VAR value1 =
    CALCULATE (
        SUM ( LOCATION_ADJUSTMENT_CON[Index] ),
        FILTER (
            ALL ( LOCATION_ADJUSTMENT_CON ),
            LOCATION_ADJUSTMENT_CON[Country] IN currCountry
        )
    )
VAR value2 =
    CALCULATE (
        SUM ( LOCATION_ADJUSTMENT_CON[Index] ),
        FILTER (
            ALL ( LOCATION_ADJUSTMENT_CON ),
            LOCATION_ADJUSTMENT_CON[Country] IN newCountry
        )
    )
RETURN
    value1 / value2

If these also don't help, can you please share a pbix or some dummy data that keep the raw data structure? They wil help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[arsalaan]

Tried this way too, does not work