create custom table issue

[erihsehc]

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)}, union function etc. I would like to create the table as screen shot below in Power BI, the row value maybe from measure or directly from data file. I am not sure if it is a good method to create data table by using formula like {1,2,3} dax function. If I created that datatable, it should be connected with slicer. I find a problem that when I create a datatable, the slicer is not working.

I created a dummy data model below, the table on right created manually, however, it is not connected with slicer. how to make a good customized datatable connected with slicer? actually, it is a very basic function in Excel, but seems to be very difficult in Power BI, thanks

data model

Best regards

ER

[v-yulgu-msft · Accepted solution]

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