Forum Discussion
Sum column based on value of another column, include filter context
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.
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.
2 Replies
- LivioLanzo
Solution Sage
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.