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

Rank the sum of each column and visualize in a sorted 'top N' horisontal bar chart

I'm banging my head against the wall on this one. I thought it would be easy, but for some reason I cannot get it to work. 

Consider the following table

timestamp                    Instrument A      Instrument B     Instrument C     Instrument D
10.01.2021 10:00:005699
10.01.2021 11:00:006239
11.01.2021 10:00:007749
12.01.2021 10:00:005334
13.01.2021 10:00:003878
14.01.2021 10:00:0096

3

4
14.01.2021 11:00:004484


What I want to do is 
1. Sum the value of each colum (in this case that would be 39, 36, 37, 47 from left to right)
2. Show the sums in an 'Top N' chart sorted decending 

 

topN chart.png

In my real world case I have some 20 columns and 50000 rows, but the principle is the same. 

 

Any tips ?

1 ACCEPTED SOLUTION
SmithT
Frequent Visitor

Hello,

i have tried out this topic and i think i have way you can do it.

What i did is, i went into PowerQuery and unpivoted the table


First it looks like your table and afterwards like this screenshot:

 

SmithT_0-1618323782163.png



And this would already work for getting the horizontal bar chart

Axis would be your Attribute (Instrument)
Value the Value 

SmithT_1-1618323878551.png



Now you can add a Top N Filter on the visualisation.

Does this help you?

Cheers

Tyree



 

View solution in original post

2 REPLIES 2
Kahrax
Frequent Visitor

Thanks for your feedback 🙂 
Yeah, this would probably work. My current problem is that all the columns are calculated colums in DAX. The columns does not excist in the power query environment. So using your solution I would need to be able to make the calculated colums in power query. It can probably be done. 

The "less work approach" would be to do this in DAX, if possible 🙂 


SmithT
Frequent Visitor

Hello,

i have tried out this topic and i think i have way you can do it.

What i did is, i went into PowerQuery and unpivoted the table


First it looks like your table and afterwards like this screenshot:

 

SmithT_0-1618323782163.png



And this would already work for getting the horizontal bar chart

Axis would be your Attribute (Instrument)
Value the Value 

SmithT_1-1618323878551.png



Now you can add a Top N Filter on the visualisation.

Does this help you?

Cheers

Tyree



 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors