Is it possible to get the data for your AUM sheet in a non aggregated manner? Because of how your data is shaped and pre-aggregated, what you ask is not going to be trivial. Since your "categories" are actually separate measures in your AUM sheet, you cant essentially use that as part of your filter action. You need a dimension that can be mapped across the two data sources in order to achieve what you need.
And another question - what dimension in your detail data matches up to the Year in the summary? You will need that dimension in addition to reshaping the data to make the networth categories as a separate dimension.
I appreciate your input. This way I am trying to get the aggregate AUM data into non-aggreggate. Once I achieve that I will keep you posted and ask further.
Again thanks for your input,
I looked into your workbook a little bit more ... I believe you essentially want both the Year and the net worth categories to be filters. In order for that to happen, you need to reshape your data to have net worth category as a dimension. Additionally - you want to match the Category names across both datasets. You have different syntax for this across the two datasets.
That still leaves us with the challenge of matching up the right dates in the rows in your detail to the right "years" in your summary. This may get tricky. ALthough, I am now wondering why you need the summary info to begin with? Cant that info be aggregated from the detail? or are you validating data across two sources?
Okay so I changed the syntax of the category in both data sets to match. Moreover, I believe that "category" is a dimension calculated field in the data sheet.
Regarding the filters, you are right however regarding the year filter, I only need the drill down action on 2017, however as you mentioned it would be according to net worth category.
I need that detail info for the end user to see in detail who holds certain accounts and how much they hold in that particular account. Therefore, back to your question I believe I am validating data across both sources since I want the end user to get that detailed information once they double click those bars.
Did you reshape the data ? By Reshaping - I mean pivoting the categories and account counts to be on its own row. See attached. This makes sure that your categories are a dimension by itself, and avoids the use of "measure names".
AUM Reshaped.xlsx 10.2 KB
So by doing this will I be able to crate a drill down action on the 2017 bar charts to view the required data?
1 of 1 people found this helpful
Though I have a solution for you here, there is still a thing or two that need clarification: I think you want two criteria to drill down by - the year, and the net worth category - correct? You have several date fields in your detail.. which date field matches up to the year from the summary?
Attached is a workbook that "attempts" to address your issue. though i dont know if I have the relationships correct. I used the reshaped excel file in the attached, and kept your other data source the same. What is needed here is a "blend" of data. And the blending has to be done using the right fields. I believe you need a combination of the year, and the net worth category (at least that is the assumption I ran with).
in the attached i have done the following:
- Define A custom Blending relationship across the two data sources. You will see how this is done if you go to Data -> Edit relationship. This drives the relationship between the data sets.
- I also had to edit your calculation for the network category to match the values across the two data sets so the blend actually returned some results.
- I created an additional calculation for extracting the "year" date part on both data sources. (this is where I made the assumption, and used the "added date" to blend on for your detail).
- On the dashboard - created a dashboard action that uses the relationship to drill down on. You can see that under Dashboard -> Actions.
Again - I need to reemphasize that I am merely showing you the technique in the attached workbook - I do not know enough about your data and the relationship, so i cannot speak to the accuracy of the results, as I made some assumptions on which date field to use.
I hope this helps!