topic Re: Sum multiple columns values based on filter in DAX Commands and Tips

Sum multiple columns values based on filter

Freemanchen — Thu, 07 Apr 2022 07:06:34 GMT

Hi,

Here's the example of the data tables I have

SolutionMap: Performance pivot table:

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

Re: Sum multiple columns values based on filter

serpiva64 — Thu, 07 Apr 2022 07:58:56 GMT

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 !

Re: Sum multiple columns values based on filter

Freemanchen — Thu, 07 Apr 2022 16:04:56 GMT

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

Re: Sum multiple columns values based on filter

serpiva64 — Thu, 07 Apr 2022 17:23:47 GMT

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 !