Forum Discussion
Summarize Data With A Table Where Columns Are Custom Date Groupings
Hi jbrewster
I've just managed to look at this again.
I've attached a sample of what I might set up if I were creating this myself.
1. The DateGroupings table is set up as described before, with a many-to-one bidirectional relationship with DateDimension.
2. If Locks, Submissions etc are measures, then we need to convert them into something that can be used as a filter. I would suggest a calculation group with each calculation item corresponding to one of the measures. I have created a calculation group called Measure Selection for this purpose. I also added a grouping column to this calculation group table to group the # and $ measures separately.
There is an example in this article of something similar.
3. Then we can construct a visual like this with the Measure Selection calculation items on the rows and Date Grouping on the columns, with any arbitrary measure in the values (I used a Dummy measure).
4. Filtering on a single cell of this matrix will filter other visuals on the page. Since on of the filters is a calculation item, it will be applied to any measures in those visuals, in this case overriding them with the measure from the relevant row.
In my example, the lower-left visual includes the selected measure (which is overridden by the calculation item), while the lower-right visual just includes column values but a visual-level filter that Dummy is not blank (which ensures it returns rows related to the selection in the matrix).
This is just to illustrate the concept and I expect this will need to be adjusted quite a bit to get the exact behaviour you want 🙂
Regards
Thanks 🙂 Just to make sure I understand this correctly, could you mock up how the 9 dates with 9 custom groupings could look, with an indication of what should be computed in each cell?
Just want to make sure I don't go astray from your requirements!
Of course. Here is a screenshot of the system we are replacing with this PowerBI report.
You can ignore the 'X' out columns. Also, in this screenshot, the activites (Locks, Submissions, ect...) are broken out into both units and dollars. The layout of that data isn't required to look exactly like as above but both the units and dollars should be there. The units is a count, the dollars is a sum.
Hope all this makes sense.
Thank you very much.
Hi jbrewster
I've just managed to look at this again.
I've attached a sample of what I might set up if I were creating this myself.
1. The DateGroupings table is set up as described before, with a many-to-one bidirectional relationship with DateDimension.
2. If Locks, Submissions etc are measures, then we need to convert them into something that can be used as a filter. I would suggest a calculation group with each calculation item corresponding to one of the measures. I have created a calculation group called Measure Selection for this purpose. I also added a grouping column to this calculation group table to group the # and $ measures separately.
There is an example in this article of something similar.
3. Then we can construct a visual like this with the Measure Selection calculation items on the rows and Date Grouping on the columns, with any arbitrary measure in the values (I used a Dummy measure).
4. Filtering on a single cell of this matrix will filter other visuals on the page. Since on of the filters is a calculation item, it will be applied to any measures in those visuals, in this case overriding them with the measure from the relevant row.
In my example, the lower-left visual includes the selected measure (which is overridden by the calculation item), while the lower-right visual just includes column values but a visual-level filter that Dummy is not blank (which ensures it returns rows related to the selection in the matrix).
This is just to illustrate the concept and I expect this will need to be adjusted quite a bit to get the exact behaviour you want 🙂
Regards