Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone,
I am working on my first report for my small open source project for personal finance analysis.
I have created a little app which extracts data from my bank account and assigns certain tags to each entry - the tags indicate what kind of spending was it.
Each spending can have a bunch of tags, and the tags are not related to each other in a strict hierarchical way. Sample below:
I'd like to create charts on my report where I could specify which tags I want to include - for example, I want to be able to see a breakdown of how much I spend on Health, Travel and Food - in which case it should include the entries which contain these tags (rows 2,5,6,8,10 and 11).
I also want to see a breakdown of my 'Car' spendings (how much is spent of fuel, how much on Service), in which case I would want the rows 6,8,9 to be included.
Last example, I want to be able to see the spendings per person (where a person tag is assigned), in which case I would like to see rows 1,6,8,9 to be included.
Ideally, I'd like to be able to drill down within those charts to see further details - for example, on the 'per person' chart, I'd like to see what kind of categories (or even entries) are included for Jack - that should show a 'Bills' and 'Travel' categories etc.
I tried with spliting the delimited data into rows, but that didn't get me anywhere.
Sample file availalable here https://filebin.ca/5hJlmHvfZtRg/Sample.xlsx
Solved! Go to Solution.
Hi @bjarmuz ,
I duplicated the Tags column then made a split by delimeter to rows. Added the following measure:
Total Value =
SUMX (
SUMMARIZE (
'Table';
'Table'[Entry ID];
'Table'[Date];
'Table'[Recipient];
'Table'[Tags];
"Value"; AVERAGE ( 'Table'[Amount] )
);
[Value]
)
Now just use the duplicated column (tagsv) for the slicer and the measure for calculations and visualizations.
Be aware that the use of the other fields in table visualizaitons or matrix you may need the option don't summarize
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @bjarmuz ,
I duplicated the Tags column then made a split by delimeter to rows. Added the following measure:
Total Value =
SUMX (
SUMMARIZE (
'Table';
'Table'[Entry ID];
'Table'[Date];
'Table'[Recipient];
'Table'[Tags];
"Value"; AVERAGE ( 'Table'[Amount] )
);
[Value]
)
Now just use the duplicated column (tagsv) for the slicer and the measure for calculations and visualizations.
Be aware that the use of the other fields in table visualizaitons or matrix you may need the option don't summarize
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey @bjarmuz
From first look, your table requires restructuring to get all the relevant answers. For example, how will you distribute the spend amount where the number of tags are more than 1?
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |