Hi there,
I'm looking to creating a dashboard which an end user can use to compare two sets of data.
The underlying data is in one table. The idea is that the end user could select their own customer and see total workflows and workflow time by workflow type and then compare to an average with the user able to select a cohort on which the average would be calculated (total, region, workflow types, etc.).
I initially tried to use DAX queries with selectedvalue but it didn't work as the selected values picked up the slicer filters on both the base and compare cohort. There doesn't seem to be a way to include the slicer name in the DAX query so that only that slicer is considered.
Instead I have created an aggregated table off the initial base table in Power Query. In the dashboard, I have a matrix and the user can select their customer using a slicer (this is using the base table). I have created the same matrix but from the aggregate table and the cohort filters can be selected and I have DAX queries to show the deltas. This is working fine and I just need to format etc. I had hoped to have a visual version of the comparison however there are no relationships between the aggregate and base table (even though the aggregate table is created from the base). When I try to create relationships, it won't allow multiple many to many relationships. Is there any way to solve this and have the aggregate table linked to the underlying base table so that I can do a visual compare?
Any advise would be appreciated.
