topic Re: Rank the sum of each column and visualize in a sorted 'top N' horisontal bar chart in DAX Commands and Tips

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

Kahrax — Tue, 13 Apr 2021 13:39:48 GMT

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:00	5	6	9	9
10.01.2021 11:00:00	6	2	3	9
11.01.2021 10:00:00	7	7	4	9
12.01.2021 10:00:00	5	3	3	4
13.01.2021 10:00:00	3	8	7	8
14.01.2021 10:00:00	9	6	3	4
14.01.2021 11:00:00	4	4	8	4

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

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

Any tips ?

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

SmithT — Tue, 13 Apr 2021 14:25:38 GMT

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:

And this would already work for getting the horizontal bar chart

Axis would be your Attribute (Instrument)
Value the Value

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

Does this help you?

Cheers

Tyree

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

Kahrax — Wed, 14 Apr 2021 06:46:34 GMT

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 🙂