我有两张表如下。
代理人表:
AgentNum AgentName
1 A1
2 A2
3 A3
销售表:
ProductNumber AgentNumber NewSales
P1 1 60
P1 2 40
P2 1 100
P3 1 40
P3 2 30
P3 3 30
在上面的示例中,具有共享的多个代理商可以销售相同的产品。例如,由3个代理商以40,30,30份出售的P3产品。两个表都使用代理商编号相关。
我需要按如下方式计算销售额:
1)代理商销售额作为其份额 - 例如,代理商1,销售额将是(对于产品p1 - 60,p2 - 100,p3 - 40)200美元。
2)相关代理商销售(例如,与代理商1合作过) :销售额将是(对于p1 -40,p2-0,p3 -60)$ 100
任何人都可以用Dax逻辑解释我吗?
解决方案
您好Sarayu_CM
您想获得如下结果吗?
你可以设置如下所示的关系
然后使用以下度量
度量2 = IF(SELECTEDVALUE(代理[AgentName])=" A1",SUM(Sales [NewSales]), CALCULATE(SUM(销售[NEWSALES]),FILTER(ALLSELECTED(代理),A gent [AgentName]<>" A1")))最好的问候, Zoe Zhi
Hi,
I have two tables as below.
Agent table:
AgentNum AgentName
1 A1
2 A2
3 A3
Sales Table:
ProductNumber AgentNumber NewSales
P1 1 60
P1 2 40
P2 1 100
P3 1 40
P3 2 30
P3 3 30
In the above example, same product can be sold by multiple agents with a share. For example, P3 product sold by 3 agents with 40,30,30 share. Both tables are related using Agent number.
I need to calculate sales as below:
1) Agent Sales as his share - For example, for agent 1, the sales amount will be (For product p1 - 60, p2 - 100, p3 - 40) $200.
2) Related Agents Sales (Who worked with Agent 1 for example): The sales amount will be (For p1 -40, p2 - 0, p3 -60) $100
Can anyone explain me with Dax logic for this?
解决方案Hi Sarayu_CM
Did you want to get result like below?
You could set relationship like below
Then use below measure
Measure 2 = IF(SELECTEDVALUE(Agent[AgentName])="A1",SUM(Sales[NewSales]),CALCULATE(SUM(Sales[NewSales]),FILTER(ALLSELECTED(Agent),Agent[AgentName]<>"A1")))Best Regards, Zoe Zhi
更多推荐
需要帮助DAX
发布评论