Sum multiple columns values based on filter

[Freemanchen]

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 .

[serpiva64 · Accepted solution]

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 !

[Freemanchen · Accepted solution]

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

[serpiva64 · Accepted solution]

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 !

[Freemanchen · Accepted solution]

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

[serpiva64 · Accepted solution]

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 !