cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RBPM
New Member

Calculate Column Values Based on multiple selection slicers

Hi,

I need to create a chart that combine information from different tables, that will be selected individually by the user. For example, I have 2021data and 2022data; two slicers will indicate the selection of the user for types2021 and for types2022. I want to create a chart that will show in y axis the sum of data2021 and sum of data2022 for the selected types on slicers, and on x axis the years 2021 and 2022. 

 

2021data:   

types2021

data2021   

A

1

B

2

C

3

D

4

E

5

F

1

G

2

H

3

I

4

J

5

 

2022data:   

types2022

data2022  

A

1

B

2

C

3

D

4

E

5

F

1

G

2

H

3

I

4

J

5

 

My idea is to create two measures to calculate the sum of data2021 and sum of data2022 (filtered by the selection), and use this values to input on a new column in the support table.

support table

years

Column

2021

 

2022

 

 

I tried these measures and column, but my answer for the measures is always "30". it seems it is not considering the selection on slicer as the sum of data considering all types is 30. I think I've created the measure wrong... not so used to it.

 

selected2021 = calculate(SUM('2021data'[data2021]),ALLSELECTED('2021data'[types2021]))

selected2022 = calculate(sum('2022data'[data2022]),ALLSELECTED('2022data'[types2022]))

Column = if('support'[years]=2021,[selected2021],[selected2022])

 

Can anyone help me? 

1 ACCEPTED SOLUTION
TarunSharma
Resolver III
Resolver III

Hello @RBPM 
You can download the file 

View solution in original post

2 REPLIES 2
TarunSharma
Resolver III
Resolver III

Hello @RBPM 
You can download the file 

View solution in original post

amitchandak
Super User IV
Super User IV

@RBPM , One way is Add One year column to these two tables in the power query and merge them.

 

The second is to add the year column in the power query. And then create common user and year tables. Join both of them with these two tables to create star schema. You should be able to show information together

 

refer few of my video for that

https://www.youtube.com/watch?v=Bkf35Roman8

https://www.youtube.com/watch?v=vYxWyt0qNUQ

https://www.youtube.com/watch?v=kU2M1LmNvNo



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors