Row base calculation wrt the radio buttons in PowerBI

Question

I was a Tableau user and I started to use PowerBI recently. However, I have some troubles about the way to make some inner calculations.

Say I have got a toy data with 5 columns as below,

Data	Type1	Type2	Currency1	Currency2
5	2	3	1.1	0.7
10	9	-5	1.3	0.74
8	12	22	1.6	0.8

What I want to achieve is that creating a new column (it was called calculation field in Tableau), by using some radio buttons (called parameter field in Tableu) as follows,

Radio Button 1 : Includes Type1 and Type2

Radio Button 2 : Includes Currency1 and Currency2

My new calculation has to be done wrt the selections of those calculations. Say, if Type2 and Currency1 selected, I want to get product of the data column with the selected ones as a new column like,

Data	Type1	Type2	Currency1	Currency2	NewColumn
5	2	3	1.1	0.7	16.5
10	9	-5	1.3	0.74	-65
8	12	22	1.6	0.8	281.6

Of course It should be dynamic with the combinations of the selected ones.

What Have I tried ?

I created the buttons and achieved the single by the guide of this link.

Currency = 
SWITCH(
  SELECTEDVALUE(Table1[Id])
   ,1,Data[Currency1]
   ,2,Data[Currency2]
)

Type= 
SWITCH(
  SELECTEDVALUE(Table2[Id])
   ,1,Data[Type1]
   ,2,Data[Type2]

But It did not work of course. Since It needs SUM etc in the calculation.

My final goal is to use that dynamically created new column in my graphs, tables etc.

I also thought wide-long format transformation but I am not so sure!

Any help is appreciated!

score 2 · Accepted Answer · answered Jan 08 '21 at 12:54

You can't use calculated columns to achieve this because data shown in columns is static.

You need to create a measure, so in order to do that:

I first created 2 tables as you did that only contains id columns and I used to create 2 slicers. Then i used the following measure:

Measure = 
VAR __selectedType = SELECTEDVALUE( Table1[ID] )
VAR __selectedCurrency = SELECTEDVALUE( Table2[ID] )

VAR __type =
SWITCH(
    __selectedType,
    1, SUM( 'Table'[Type1] ),
    2, SUM( 'Table'[Type2] ),
    0
)

VAR __currency =
SWITCH(
    __selectedCurrency,
    1, SUM( 'Table'[Currency1] ),
    2, SUM( 'Table'[Currency2] ),
    0
)

Return __type * __currency * SUM( 'Table'[Data] )

Then drag the measure in a table visual. This is the expected result.

Row base calculation wrt the radio buttons in PowerBI

1 Answers1