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

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

Reply
Freemanchen
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
serpiva64
Super User
Super User

Hi,

to obtain something like this

serpiva64_0-1649316317117.png

serpiva64_1-1649316342769.png

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

serpiva64_2-1649316504894.png

- 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]
)
)

 

serpiva64_0-1649318326043.png

 

 

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 !

 

 

 

View solution in original post

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

View solution in original post

You have only to create a relationship between your two table

serpiva64_0-1649352028766.png

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

serpiva64_2-1649352213728.png

 

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 !

 

View solution in original post

3 REPLIES 3
serpiva64
Super User
Super User

Hi,

to obtain something like this

serpiva64_0-1649316317117.png

serpiva64_1-1649316342769.png

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

serpiva64_2-1649316504894.png

- 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]
)
)

 

serpiva64_0-1649318326043.png

 

 

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 !

 

 

 

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

You have only to create a relationship between your two table

serpiva64_0-1649352028766.png

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

serpiva64_2-1649352213728.png

 

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 !

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors