Total revenue generated per year combining multiple roles?

[eegarlepp]

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

[TeigeGao · Accepted 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

[eegarlepp]

@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?

[eegarlepp]

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