cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eegarlepp Frequent Visitor
Frequent Visitor

Total revenue generated per year combining multiple roles?

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.

 

IDOpptyRevenuePrimarySecondaryYear
12Oppty A $    1,000.00EdBill2017
14Oppty B $    2,000.00Jane 2017
16Oppty C $    3,000.00MaryEd2017
18Oppty D $    2,000.00EdKate2017
20Oppty E $    5,000.00Bill 2018
22Oppty F $    6,000.00Kate 2018
24Oppty G $    8,000.00Ed 2018
26Oppty H $  10,000.00AllyEd2018
28Oppty I $  12,000.00MarkAlly2018
30Oppty J $  14,000.00JeffJane2018
32Oppty K $  16,000.00JamesMary2019
34Oppty L $  18,000.00EdKate2019
36Oppty M $  20,000.00Kathy 2019
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Total revenue generated per year combining multiple roles?

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:

Snipaste_2019-04-09_14-36-15.png

Best Regards,

Teige

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Total revenue generated per year combining multiple roles?

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:

Snipaste_2019-04-09_14-36-15.png

Best Regards,

Teige

View solution in original post

eegarlepp Frequent Visitor
Frequent Visitor

Re: Total revenue generated per year combining multiple roles?

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   
PersonRevenue as PrimaryRevenue as SecondaryTotal 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

 

 

eegarlepp Frequent Visitor
Frequent Visitor

Re: Total revenue generated per year combining multiple roles?

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 370 members 3,399 guests
Please welcome our newest community members: