英文:
Power BI Summarize by Unique ID and allow filtering on other dimensions
问题
我有一个数据集，每个'case'都有一个唯一的ID，但是每个'case'可以与多个维度关联，因此有多行数据。但是由于只有一个唯一的ID或case，当将其与其他金额相加时，在下面的场景中，BR_1只需计算一次，即£4000。我见过的所有解决方案（SUMMARIZE、CALCULATE）仅适用于有限的场景，但解决方案需要是动态的，这样如果有人选择了HR、Alpha、Customer，则总金额将为4500（如果选择了Sales、Alpha、Customer，则总额为£4000）。
英文:
I have a dataset that has a unique ID for each 'case' but there are multiple rows as each 'case' can be linked to several dimensions. But as there is only one unique ID or case, when summing this with other amounts, in the scenario below, BR_1 needs to be counted only once as £4000. All the solutions I've seen (SUMMARIZE, CALCULATE) works only for a finite scenario but the solution needs to be dynamic so that if someone say selected HR, Alpha, Customer the total amount would be 4500. (And if they selected Sales, Alpha Customer, the total is £4000.
答案1
得分: 1
你可以使用以下的方法：
SUMX(SUMMARIZE(table, table[ID], table[Amount]), table[Amount])
这将适用于不同类别中的任何过滤器组合，并且只会计算每个金额一次。但是，前提是每个ID要么始终具有相同的金额，要么在相同ID的“案例”中只有一行，并且强制不同的过滤器始终被选择。
英文:
You can use the following measure:
SUMX(SUMMARIZE(table, table[ID], table[Amount]), table[Amount])
This will work with any combination of filters in the different categories and will only count each amount once. The caveat is that either each ID has to have always the same Amount or you need to have only one row per "case" for the same id and force the different filters to always be selected.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论