Forum Discussion
Helper ISum 2 ranked columns together
Hi there,
I am interested in summing 2 ranked columns together.
I have 1 data table wtih date and usage.
Specific dates are selected, I have different dates filtered on the 2 visuals and have them ranked based on hourlyusage.
Once its ranked, I want to sum the 2 visual's hourly usage. So rank 1 + rank 1, rank 2+rank 2 etc.
so results would be rank 1 would be 4327.55, 2 would be 4270.96 etc
6 Replies
- lbendlin
Super User
What have you tried and where are you stuck? Are these columns in the same data table?
mikoalWhat have you tried and where are you stuck?
I have tried measures, adding a column.
I'm stuck getting a summed result based on 2 ranked visual results.
The ranked results are of different time frames. But I want to add up rank 1 from one visual with rank 1 of another visual.
The column date and hourlyusage is in the same table.
What DAX statement? I don't see any DAX from you. ,
- lbendlin
Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523- mikoal
lbendlin Ok I will try to explain this
First is my sample data with a date range and usage data
Date Data 2022-01-01 10:00 1 2022-01-01 11:00 12345 2022-01-01 12:00 2 2022-01-01 13:00 98 2022-01-01 14:00 3 2022-01-01 15:00 66 2022-01-01 16:00 4 2022-01-01 17:00 849 2022-01-01 18:00 5 2022-01-01 19:00 16 2022-01-01 20:00 6 2022-01-01 21:00 88 2022-01-01 22:00 55 2022-01-01 23:00 22 2022-01-02 0:00 0 2022-01-02 1:00 15 2022-01-02 2:00 18 2022-01-02 3:00 81 2022-01-02 4:00 56 2022-01-02 5:00 45 2022-01-02 6:00 123 2022-01-02 7:00 1234 2022-01-02 8:00 1114 2022-01-02 9:00 888 2022-01-02 10:00 456 2022-01-02 11:00 2 2022-01-02 12:00 98 2022-01-02 13:00 3 2022-01-02 14:00 66 2022-01-02 15:00 4 2022-01-02 16:00 849 2022-01-02 17:00 5 2022-01-02 18:00 16 2022-01-02 19:00 6 2022-01-02 20:00 88 2022-01-02 21:00 55 2022-01-02 22:00 22 2022-01-02 23:00 0 2022-01-03 0:00 15 2022-01-03 1:00 18 2022-01-03 2:00 81 Then after that, I would break it down into different custom date ranges.
In those custom date range, I would sort it by the max usage. Below is 2 filtered/sorted visuals. The user can select different date ranges and up to 5 different sets of data.
Date Data Date Data 2022-01-01 11:00 12345 2022-01-02 7:00 1234 2022-01-02 7:00 1234 2022-01-02 8:00 1114 2022-01-02 8:00 1114 2022-01-02 9:00 888 2022-01-02 9:00 888 2022-01-02 16:00 849 2022-01-01 17:00 849 2022-01-02 10:00 456 2022-01-02 10:00 456 2022-01-02 6:00 123 2022-01-02 6:00 123 2022-01-02 12:00 98 2022-01-01 13:00 98 2022-01-02 20:00 88 2022-01-01 21:00 88 2022-01-02 3:00 81 2022-01-02 3:00 81 2022-01-02 14:00 66 2022-01-01 15:00 66 2022-01-02 4:00 56 2022-01-02 4:00 56 2022-01-02 21:00 55 2022-01-01 22:00 55 2022-01-02 5:00 45 2022-01-02 5:00 45 2022-01-02 22:00 22 2022-01-01 23:00 22 2022-01-03 1:00 18 2022-01-02 2:00 18 2022-01-02 2:00 18 2022-01-01 19:00 16 2022-01-02 18:00 16 2022-01-02 1:00 15 2022-01-03 0:00 15 2022-01-01 20:00 6 2022-01-02 1:00 15 2022-01-01 18:00 5 2022-01-02 19:00 6 2022-01-01 16:00 4 2022-01-02 17:00 5 2022-01-01 14:00 3 2022-01-02 15:00 4 2022-01-01 12:00 2 2022-01-02 13:00 3 2022-01-01 10:00 1 2022-01-02 11:00 2 2022-01-02 0:00 0 2022-01-02 23:00 0
Then the desired results is just summing the ranked data together
results Date Data Jan 13579 Jan 2348 Jan 2002 Jan 1737 Jan 1305 Jan 579 Jan 221 Jan 186 Jan 169 Jan 147 Jan 122 Jan 111 Jan 100 Jan 67 Jan 40 Jan 36 Jan 32 Jan 30 Jan 21 Jan 11 Jan 9 Jan 7 Jan 5 Jan 3 Jan 0
