Forum Discussion

nirrobi's avatar
nirrobi
Icon for Helper V rankHelper 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 ...
Need Help
  • v-yulgu-msft's avatar
    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

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft 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's avatar
nirrobi
Icon for Helper V rankHelper 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's avatar
    nirrobi
    Icon for Helper V rankHelper V
    9 years ago

    I reproduce from scratch and I works smoothly.

     

    many thanks,

     

    Nir