Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
eegarlepp
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
TeigeGao
Solution Sage
Solution Sage

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
TeigeGao
Solution Sage
Solution Sage

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

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

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.