cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
__fab__
Frequent Visitor

SELECTCOLUMNS performs wrong calculation on some tables

Hi All,

I am getting crazy by a weird behaviour of SELECTCOLUMNS with some - but not all - tables in my dataset.
Since I need to split my data on a yearly basis, I use SC to create a new table with just required columns.
The problem is that same formula produces different outcomes:
- in some cases, values are entered in respective columns (2020 values in 2020 col, 2021 values in 2021 col, and so on)

- in other cases, the same formula has sum up all values of respective years, then populated the new table with these totals

 

For sure I am doing something wrong, but I just can't figure out where...

 

Many thanks in advance to whose can provide any help/hint.

 

expected outcomeexpected outcomeweird behaviourweird behaviour

 

 

1 ACCEPTED SOLUTION
__fab__
Frequent Visitor

Hi All,

 

for all those interested, root cause and solution of the issue can be found in this post: Same Formula applied on identical Tables gives different outcome 

 

 

View solution in original post

3 REPLIES 3
__fab__
Frequent Visitor

Hi All,

 

for all those interested, root cause and solution of the issue can be found in this post: Same Formula applied on identical Tables gives different outcome 

 

 

amitchandak
Super User
Super User

@__fab__ , Use summarize

example

 

summarize(Table, Table[Date], "2020", calculate(sum(Table[Value]), filter(Table, Table[Year] =2020) )
, "2021", calculate(sum(Table[Value]), filter(Table, Table[Year] =2021) )
, "2022", calculate(sum(Table[Value]), filter(Table, Table[Year] =2022) ))



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

@amitchandak

thanks for your reply; however, though your solution works for different purposes, I don't need the years' totals but all the rows for the same period (see picture 'expected outcome').

In addition, please note that the same logic works with some tables but not with others, so I wonder whether the problem is not in which formula is going to use but in the data/table; here is were I am getting lost.

not the expected resultnot the expected result

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors