cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Sum multiple columns values based on filter

Hi,

Here's the example of the data tables I have

SolutionMap:                                    Performance pivot table:

|Solutionid(distinct) | Workload                      |Partner id     | 1                | 2

| 1               | A                                                 |1                  | 250            | 180    .....   .

| 2               | A                                                 |2                  | 150            | 100

| 3               | B                                                 |3                  | 100            |  80

| 4               | C

....

The Performance pivot table has partner id and the revenue for each Solutionid, I need some help to create a measure table that will sum the revenue base on the SolutionID I picked from the list filter ,

For example, if I select Solution ID 1, it should show:

|Partner id     | Total_revenue

|1                  | 250

|2                  | 150

|3                  | 100

if I selected Solution ID 1,2, it should show:

|Partner id     | Total_revenue

|1                  | 430

|2                  | 250

|3                  | 180

How should I do it?

Thank you .

3 ACCEPTED SOLUTIONS
Super User

Hi,

to obtain something like this

the easiest solution is to unpivot column in performance table
- select your SolutionID columns and then unpivot

- rename the column you obtain conveniently and load.

You can obtain the same thing in DAX by creating a new table

Table2 =
UNION (
SELECTCOLUMNS (
'Table (3)',
"PartnerID", 'Table (3)'[PartnerID],
"SolutionID", "1",

"Revenue", 'Table (3)'[1]
),
SELECTCOLUMNS (
'Table (3)',
"PartnerID", 'Table (3)'[PartnerID],
"SolutionID", "2",

"Revenue", 'Table (3)'[2]
)
)

If this post is useful to help you to solve your issue consider giving the post a thumbs up

and accepting it as a solution !

Frequent Visitor

thank you that's really help, is there anyway I could apply a workload filter on this too?

Super User

You have only to create a relationship between your two table

then add Workload as a slicer and now you can slice and dice as you want

If this post is useful to help you to solve your issue consider giving the post a thumbs up

and accepting it as a solution !

3 REPLIES 3
Super User

Hi,

to obtain something like this

the easiest solution is to unpivot column in performance table
- select your SolutionID columns and then unpivot

- rename the column you obtain conveniently and load.

You can obtain the same thing in DAX by creating a new table

Table2 =
UNION (
SELECTCOLUMNS (
'Table (3)',
"PartnerID", 'Table (3)'[PartnerID],
"SolutionID", "1",

"Revenue", 'Table (3)'[1]
),
SELECTCOLUMNS (
'Table (3)',
"PartnerID", 'Table (3)'[PartnerID],
"SolutionID", "2",

"Revenue", 'Table (3)'[2]
)
)

If this post is useful to help you to solve your issue consider giving the post a thumbs up

and accepting it as a solution !

Frequent Visitor

thank you that's really help, is there anyway I could apply a workload filter on this too?

Super User

You have only to create a relationship between your two table

then add Workload as a slicer and now you can slice and dice as you want

If this post is useful to help you to solve your issue consider giving the post a thumbs up

and accepting it as a solution !