Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I currently have the following dataset:
Dataset | |||||
Agent | Agent (Pair) | Pair Index | Week | Sales | Product |
Cory | Cory | 11-Feb | 1000 | A | |
Angela | Angela | 11-Feb | 2000 | A | |
Pam | Pam, Jim | 1 | 04-Feb | 3000 | A |
Jim | Jim, Pam | 1 | 11-Feb | 4000 | A |
Michael | Michael, Tony | 2 | 04-Feb | 5000 | A |
Tony | Tony, Michael | 2 | 04-Feb | 6000 | A |
Sam | Sam | 11-Feb | 7000 | C |
I want to create a table that
1) filters out Product C,
2) has the paired agents (the second column which is based on their pair index),
3) and the average of the sales of each pair (and if they're not a pair just their own sales).
Desired Results | |
Agent (Pair) | Sales |
Cory | 1000 |
Angela | 2000 |
Pam, Jim | 3500 |
Jim, Pam | 3500 |
Michael, Tony | 5500 |
Tony, Michael | 5500 |
In this scenario, when I filter on Feb 11, it would take out both Michael and Tony. Since Jim has made $4000 in this week, both "Jim, Pam" and "Pam, Jim" would be in this table with $2000 sales each ($4000/2).
Desired Results for Feb 11 | |
Agent (Pair) | Sales |
Cory | 1000 |
Angela | 2000 |
Pam, Jim | 2000 |
Jim, Pam | 2000 |
How would you go about creating a measure for the sales? I'm currently using a calculated column, but I cannot filter based on Week.
Thank you so much for your time and I apologize for the complexity.
Solved! Go to Solution.
Hello @Anonymous
I have uploaded a pbix file for you here: https://1drv.ms/u/s!AiiWkkwHZChHj1_GgTacH8l6Qsxi
Basically, you build the model model:
and then add this measure:
AvgSales = SUMX( AgentsPairs, CALCULATE( SUM( Sales[Sales] ), CROSSFILTER( AgentsPairs[Agent1], Agents[Agent], Both ) ) * AgentsPairs[Ratio] )
Please see the file I uploaded for more info.
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hello @Anonymous
I have uploaded a pbix file for you here: https://1drv.ms/u/s!AiiWkkwHZChHj1_GgTacH8l6Qsxi
Basically, you build the model model:
and then add this measure:
AvgSales = SUMX( AgentsPairs, CALCULATE( SUM( Sales[Sales] ), CROSSFILTER( AgentsPairs[Agent1], Agents[Agent], Both ) ) * AgentsPairs[Ratio] )
Please see the file I uploaded for more info.
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hello,
Thank you so much! The original request was modified so I had to go a different route, but your solution would have fixed my issue.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |