Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Trying to show total revenue generated for a particular individual taking into account their support whether as a Primary or Secondary supporter for an Oppty by Year. For example, Ed, Ihave created a Matrix showing the revenue by year for him as a Primary and as a secondary by way of the matrix options. I would like to combine EDs total revenue for each year combining the revenue for when he was a primary or a secondary. Let me know if you need clarification further.
ID | Oppty | Revenue | Primary | Secondary | Year |
12 | Oppty A | $ 1,000.00 | Ed | Bill | 2017 |
14 | Oppty B | $ 2,000.00 | Jane | 2017 | |
16 | Oppty C | $ 3,000.00 | Mary | Ed | 2017 |
18 | Oppty D | $ 2,000.00 | Ed | Kate | 2017 |
20 | Oppty E | $ 5,000.00 | Bill | 2018 | |
22 | Oppty F | $ 6,000.00 | Kate | 2018 | |
24 | Oppty G | $ 8,000.00 | Ed | 2018 | |
26 | Oppty H | $ 10,000.00 | Ally | Ed | 2018 |
28 | Oppty I | $ 12,000.00 | Mark | Ally | 2018 |
30 | Oppty J | $ 14,000.00 | Jeff | Jane | 2018 |
32 | Oppty K | $ 16,000.00 | James | Mary | 2019 |
34 | Oppty L | $ 18,000.00 | Ed | Kate | 2019 |
36 | Oppty M | $ 20,000.00 | Kathy | 2019 |
Solved! Go to Solution.
Hi @eegarlepp ,
According to your description, my understanding is that you want to count the total Revenue for each person every year whether he is primary or secondary.
In this scenario, we can first create a person table like below:
Individual = DISTINCT ( UNION ( FILTER ( VALUES ( Table1[Primary] ), LEN ( Table1[Primary] ) > 0 ), FILTER ( VALUES ( Table1[Secondary] ), LEN ( Table1[Secondary] ) > 0 ) ) )
The create a relationship between this table and original table, then create a measure using the below DAX query:
Measure = CALCULATE ( SUM ( Table1[Revenue] ), FILTER ( ALLEXCEPT ( Table1, Table1[Year] ), Table1[Primary] = MIN ( Individual[Person] ) || Table1[Secondary] = MIN ( Individual[Person] ) ) )
The result will like below:
Best Regards,
Teige
Hi @eegarlepp ,
According to your description, my understanding is that you want to count the total Revenue for each person every year whether he is primary or secondary.
In this scenario, we can first create a person table like below:
Individual = DISTINCT ( UNION ( FILTER ( VALUES ( Table1[Primary] ), LEN ( Table1[Primary] ) > 0 ), FILTER ( VALUES ( Table1[Secondary] ), LEN ( Table1[Secondary] ) > 0 ) ) )
The create a relationship between this table and original table, then create a measure using the below DAX query:
Measure = CALCULATE ( SUM ( Table1[Revenue] ), FILTER ( ALLEXCEPT ( Table1, Table1[Year] ), Table1[Primary] = MIN ( Individual[Person] ) || Table1[Secondary] = MIN ( Individual[Person] ) ) )
The result will like below:
Best Regards,
Teige
@TeigeGao Thank you! After learning PowerBI over time, I realized what you were saying and it does work. The challenge I am seeing now is that the Total column in the matrix for that measure is not showing any numbers. Any thoughts?
Thank you for replying. Sorry, I am new to PowerBi and I might not have explained that just right. Let me try it again.
The columns Primary and Secondary are within the same table along with the Opportunity column, Amount column, and the person column. The challenge i am having is trying to produce a revenue Matrix where it lists the Persons revenue as a Primary for opportunity and represents the revenue as the Secondary in a column and a third column that sums the Primary oppty amount and their name as a Secondary . The da
MATRIX | |||
Person | Revenue as Primary | Revenue as Secondary | Total Revenue |
ED | $1,000,456.98 | $600,000.00 | $1,600,456,98 |
Jane | $1,100,456.98 | $700,000.00 | $1,800,456,98 |
Mark | $1,200,456.98 | $700,000.00 | $1,900,456,98 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |