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
Thank you OwenAuger.
Yes, your understanding of what I want the final output to be is correct; a table of values that can be clicked to filter another table.
Once I have the period table built, what is the best way to actually visualize the information on screen? There will be multiple dates being pulled from the fact table (9 in total) to be grouped across these periods.
Is there a way to have all of that information in a single table? Is that even a good idea?
I really appreciate your help here.
Super UserHi again jbrewster
Thanks for confirming!
The best way to visualize would depend on what you are wanting to convey. Is it useful to see Today, Last Week, Last Month, Last Quarter etc all at once?
Also, what did you mean by "9 dates"? I would expect a larger number of dates in the CLI table if it relates to all those different date groupings.
In any case, I can mock up an example model/report to illustrate some ideas. Will send a bit later.
Regards
Yes, all at once is the goal here.
As for the dates, there are 9 dates that need to be grouped into those custom groupings. That would mean 81 total "cells" to speak of on the page.
Thank you very much!
- OwenAuger
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.