Forum Discussion

Icon for Helper V rank

Helper V

9 years ago

Solved

What If Analysis - Data Table - similar to exel function

Hi all,

I want to implement in PBI the same function as in excel .

I have 2 variables that effect measure and what to see in one table how the change of those 2 variables effect the calculation of the measure.

in excel its located in the following path:

data ribbon

what if

data table

for example multiplication table.

C4 - X

B4 - Y

in the first line of the table in the left - C4 * B4

the function excel create automatic:

{=TABLE(C4,B4)}

output

1	1	2	3	4	5	6	7	8	9	10
1	1	2	3	4	5	6	7	8	9	10
2	2	4	6	8	10	12	14	16	18	20
3	3	6	9	12	15	18	21	24	27	30
4	4	8	12	16	20	24	28	32	36	40
5	5	10	15	20	25	30	35	40	45	50
6	6	12	18	24	30	36	42	48	54	60
7	7	14	21	28	35	42	49	56	63	70
8	8	16	24	32	40	48	56	64	72	80
9	9	18	27	36	45	54	63	72	81	90
10	10	20	30	40	50	60	70	80	90	100

Is it possible to implement it in excel?

thanks in advanced.

Nir.

v-yulgu-msft
9 years ago
Hi nirrobi,

It is not possible to generate such an data table automatically in Power BI desktop.

But we can achieve a similar output via below workaround. It would be better that the dataset imported into Power BI looks like:

Based on this basic table, you should create a calculated table using this formula:

Sheet2 = CROSSJOIN(SELECTCOLUMNS(Sheet1,"X",Sheet1[X]),SELECTCOLUMNS(Sheet1,"Y",Sheet1[Y]))

Create a measure that returns multiplication values.

multiplication = MAX(Sheet2[X])*MAX(Sheet2[Y])

In report view, insert a Matrix visual to display data.

Best regards,
Yuliana Gu

3 Replies

v-yulgu-msft
Microsoft Employee
9 years ago
Hi nirrobi,

It is not possible to generate such an data table automatically in Power BI desktop.

But we can achieve a similar output via below workaround. It would be better that the dataset imported into Power BI looks like:

Based on this basic table, you should create a calculated table using this formula:

Sheet2 = CROSSJOIN(SELECTCOLUMNS(Sheet1,"X",Sheet1[X]),SELECTCOLUMNS(Sheet1,"Y",Sheet1[Y]))

Create a measure that returns multiplication values.

multiplication = MAX(Sheet2[X])*MAX(Sheet2[Y])

In report view, insert a Matrix visual to display data.

Best regards,
Yuliana Gu
- nirrobi
  Helper V
  9 years ago
  Dear Yuliana ,
  thanks for your message.
  
  When I follow your guidence I ended with this table :
  
  I change the base table as follow:
  
  I use your formula for the new table:
  
  Sheet2 =
  CROSSJOIN(
  SELECTCOLUMNS( Sheet1, "X" , Sheet1[X]),
  SELECTCOLUMNS( Sheet1 ,"Y" , Sheet1[Y] ))
  
  and got this table - 100 rows:
  
  and the formula for the measure:
  
  multiplication = MAX( Sheet2[X] ) * MAX( Sheet1[Y] )
  
  can you please check if you see any reason for that?
  - nirrobi
    Helper V
    9 years ago
    I reproduce from scratch and I works smoothly.
    
    many thanks,
    
    Nir