Power BI使用字段参数的百分比总计

huangapple go评论114阅读模式
英文:

Power BI Percentage of Total Using Field Parameters

问题

我有一个Power BI数据集,我正在使用字段参数来过滤矩阵可视化中的数据,这个功能运行得很好。

我创建了一个单独的字段参数,可以在不同的度量之间切换。

包括的度量有:

  • 行计数
  • 总销售额
  • 总销售额的百分比

前两个度量工作得很好,但是总销售额的百分比不工作,因为我使用以下DAX计算总销售额的百分比:

% 总计 =
VAR tot =
    CALCULATE ( [AccountID Count], ALLSELECTED ( DimProduct ) )
RETURN
    DIVIDE ( [AccountID Count], tot )

除非我更改我的维度字段参数为Product,否则结果都是100%,而这实际上不是我想要的。

我需要我的总销售额百分比度量根据我选择的任何维度字段参数进行计算。

我甚至尝试将DAX更改为:

% 总计 =
VAR tot =
    CALCULATE ( [AccountID Count], ALLSELECTED ( DimensionFilter ) )
RETURN
    DIVIDE ( [AccountID Count], tot )

这也只返回了100%的结果...

英文:

I have a Power BI Dataset of which I'm using Field Parameters to filter data in a Matrix Visual, which works perfectly.

I created a separate Field Parameter whereby I can switch between different measures as well.

Measures included are

  • CountRows
  • Total Sales
  • Percentage of Total Sales

The first 2 measures works perfectly, however the percentage of total sales, does not, becuase I'm calculating the Percentage of Total Sales, using the following DAX:

% Total =
VAR tot =
    CALCULATE ( [AccountID Count], ALLSELECTED ( DimProduct ) )
RETURN
    DIVIDE ( [AccountID Count], tot )

The result is 100% for everything else, unless I change my Dimension Field Parameter to Product, which is what I actually want.

I need my Percentage of Total measure to calculate based on any Dimension Field Parameter I select.

How do I achieve this?

I even tried changing the DAX to:

% Total =
VAR tot =
    CALCULATE ( [AccountID Count], ALLSELECTED ( DimensionFilter ) )
RETURN
    DIVIDE ( [AccountID Count], tot )

Which also just returned 100% for everything...

答案1

得分: 0

我成功解决了这个问题,使用了以下方法:

DAX:

% 总计 = 
VAR al =
    CALCULATE (
        [AccountID 计数],
        ALLSELECTED ( DimDate ),
        ALLSELECTED ( DimBranch ),
        ALLSELECTED ( DimCountry ),
        ALLSELECTED ( DimCustomerService ),
        ALLSELECTED ( DimProduct )
    )
RETURN
    DIVIDE ( [AccountID 计数], al )

这个方法完美运行。

英文:

I managed to solve this using the following:

DAX:

% Total = 
VAR al =
    CALCULATE (
        [AccountID Count],
        ALLSELECTED ( DimDate ),
        ALLSELECTED ( DimBranch ),
        ALLSELECTED ( DimCountry ),
        ALLSELECTED ( DimCustomerService ),
        ALLSELECTED ( DimProduct )
    )
RETURN
    DIVIDE ( [AccountID Count], al )

This works perfectly.

huangapple
  • 本文由 发表于 2023年8月10日 17:04:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/76874200.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定