cancel
Showing results 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

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.

 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
1 ACCEPTED SOLUTION
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:

Best Regards,

Teige

3 REPLIES 3
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:

Best Regards,

Teige

Frequent Visitor

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

Frequent Visitor

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

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors