Forum Discussion
Sum 2 ranked columns together
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
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 |
Hi mikoal,
Did these filters use the same data table field?
If that is the case, you can't get different selections from slicers using the same data table field. I'd like to suggest you create an unconnected table and use it as the source of the second slicer.After these steps, you can create a parameter table with all rank values and use it to create the matrix.
Then you can write a measure experienced with two variables to summarize and rank particular selection ranges, and extract the current rank as a condition to check and aggregate two variable table values.
Regards,Xiaoxin Sheng
Hi XiaoXin
Yes they use the same data table.
But can't I use 2 different slicers to get 2 different date ranges? And 5 slicers for 5 ranges?
My main problem isn't getting the slicer to filter the data. my main problem is summing up the data of the columns into 1.
I'm not familiar with parameter table, do you have any examples of this and would this help with summing mulitple ranked time ranges?
I will also, search up some information regarding this. Thanks