What If Analysis - Data Table - similar to exel function

[nirrobi]

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 · Accepted solution]

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]

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]

I reproduce from scratch and I works smoothly.

many thanks,

Nir