Forum Discussion

erihsehc's avatar
erihsehc
Helper III
8 years ago
Solved

create custom table issue

hi there,   I would like to create a customer table in BI like using Excel, I am thinking to use  {(1,CALCULATE(SUM(Sheet1[amt]),FILTER(ALLSELECTED(Sheet1),Sheet1[item]="1.sales"))),(2,xxx),(3,xxx)...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi erihsehc,

     

    Based on my assumption, you want a calculated table like this, right?

     

    However, even we create a relationship between this calculated table and source table, the [Amount] value won't be changed according to slicer selection. Because [Amount] is initialized once we created the table. In other words, the values are static in a calculated table.

     

    Why do you need to create such a custom table? In your scenario, if you want to display value conditionally depend on slicer, you'd better use measures. For example, add [item] from 'Sheet1' into matrix rows, add measure Sum Amount=SUM(Sheet1[amt]) into matrix values. An easier way is directly add [amt] into matrix Values and choose Sum aggregation for it.

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi erihsehc,

 

Based on my assumption, you want a calculated table like this, right?

 

However, even we create a relationship between this calculated table and source table, the [Amount] value won't be changed according to slicer selection. Because [Amount] is initialized once we created the table. In other words, the values are static in a calculated table.

 

Why do you need to create such a custom table? In your scenario, if you want to display value conditionally depend on slicer, you'd better use measures. For example, add [item] from 'Sheet1' into matrix rows, add measure Sum Amount=SUM(Sheet1[amt]) into matrix values. An easier way is directly add [amt] into matrix Values and choose Sum aggregation for it.

 

Best regards,

Yuliana Gu