ideas for make display data in matrix

[snifer]

i try to explain what my goal,

month	amount	currency	amount  converted in dkk
1	1000	dkk	1000
2	500	eur	2000

my data are stored in table where sometimes currency is eur sometime dkk

 i need to build matrix that will display  eur or dkk

 to do that i would like to have a kind of slicer or something else to select what currency will be displayed in the matrix

is that possible?

i just need the possibility to display a value or other in the matrix based on a selector

i got the idea to create a measure in dax that allow me to select 1 column or 2 column, and the put this measure in the value field of the matrix

https://community.powerbi.com/t5/Desktop/Select-a-Column-From-a-Calculated-Table-Inside-a-Measure/td...

https://community.powerbi.com/t5/Desktop/DAX-Column-content-based-on-slicer-selection/td-p/378628

can anyone see if it is possible and what dax query  will allow this_?

[v-jiascu-msft]

Hi @snifer,

If you'd like to select the currency in a slicer, I would suggest you create an independent [Currencies] table. Please download the demo from the attachment.

1. Create a new table.

Currencies = VALUES(Table1[currency])

2. Create a measure.

Measure =
IF (
    SELECTEDVALUE ( Currencies[currency] ) = "eur",
    SUMX ( FILTER ( 'Table1', 'Table1'[currency] = "dkk" ), [amount] )
        / 4
        + SUMX ( FILTER ( Table1, Table1[currency] = "eur" ), [amount] ),
    IF (
        SELECTEDVALUE ( Currencies[currency] ) = "dkk",
        SUMX ( FILTER ( 'Table1', 'Table1'[currency] = "dkk" ), [amount] )
            + SUMX ( FILTER ( Table1, Table1[currency] = "eur" ), [amount] )
                * 4,
        SUM ( Table1[amount] )
    )
)

Best Regards,

[snifer]

@v-jiascu-msft

I modified your lines to make work for mine seems to work, the only "issue " is that if I want to display  a company that is already in dkk and the selector dkk is not marked then the matrix is empty

it will be nice to have the possibility that when local currency = DKK the display selecting automatically both local currency and dkk

can I modify the below code to have that?

Measure =

IF (

SELECTEDVALUE ( CurrencySelector[value] ) = "DKK",

SUMX ( FILTER ( UploadAccountsCurrentyPeriod, 'UploadAccountsCurrentyPeriod'[Currency selector] = "DKK" ), UploadAccountsCurrentyPeriod[PeriodAmountDKK] ),

IF (

SELECTEDVALUE ( CurrencySelector[value] ) = "Local Currency",

SUMX ( FILTER ( 'UploadAccountsCurrentyPeriod', UploadAccountsCurrentyPeriod[Currency selector] = "Local currency" ), UploadAccountsCurrentyPeriod[Period Amount] ),

SUM(UploadAccountsCurrentyPeriod[PeriodAmountDKK])

))

[snifer]

@v-jiascu-msft

I tried this combo but i cant make it work

Measure-total =

IF (

SELECTEDVALUE ( CurrencySelector[value] ) = "DKK",

SUMX ( FILTER ( UploadAccountsCurrentyPeriod, 'UploadAccountsCurrentyPeriod'[Currency selector] = "DKK" ), UploadAccountsCurrentyPeriod[-amount] ),

IF (

SELECTEDVALUE ( CurrencySelector[value] ) = "Local Currency",

SUMX ( FILTER ( 'UploadAccountsCurrentyPeriod', UploadAccountsCurrentyPeriod[Currency selector] = "Local currency" ), UploadAccountsCurrentyPeriod[-amount] ),

IF ( SELECTEDVALUE(CurrencySelector[value]) = "Local Currency" && IF(LOOKUPVALUE(UploadAccountsCurrentyPeriod[Currency selector],UploadAccountsCurrentyPeriod[Currency selector], "DKK"), SUM(UploadAccountsCurrentyPeriod[-amount] )

))))

[snifer]

@v-jiascu-msft

if local currency dkk 

if selected "local currency" display values in dkk (because local currency is dkk) ->[Period Amount]

if selected dkk display same values of the currency of before ->[Period Amount]

if local currency not dkk 

if selected "local currency" display local currency ->[Period Amount]

if selected "dkk" display values in dkk ->[PeriodAmountDKK]

to calculate the conversion values I made 2 measure already done and work 100%

 [Period Amount] -> local currency

and 

[PeriodAmountDKK] measure that convert in dkk

[v-jiascu-msft]

Hi @snifer,

Can you share a more accurate sample, please? Please mask the sensitive parts first.

I don't know the "local currency" from current messages.

Best Regards,

[snifer]

@v-jiascu-msft

there is a column in table 1 where is written or "dkk" or "local currency" the column is called currency selector

following your first post I create a secondary table where I do have the measure u told me and the column to select or "dkk" or "local currency" 

do all data stored in 2 table

selected value are in table 1 is the selector

and all data in table 2

if local currency dkk (table 2)

if selected (table 1)"local currency" display values in dkk (because local currency is dkk) ->(table 2)[Period Amount]

if selected (table 1)dkk display same values of the currency of before ->(table 2)[Period Amount]

if local currency not dkk (table 2)

if selected (table 1) "local currency" display local currency ->(table 2)[Period Amount]

if selected (table 1)"dkk" display values in dkk ->(table 2)[PeriodAmountDKK]

to calculate the conversion values I made 2 measure already done and work 100%

 [Period Amount] -> local currency

and 

[PeriodAmountDKK] measure that convert in dkk

[v-jiascu-msft]

Hi @snifer,

Can you share your file? Please mask the sensitive parts. You only need to keep a few records. I think we have done the same thing. It could be something wrong with other parts. 

I had attached a file. You can also modify it and point out the issues.

Best Regards,