cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bjarmuz
New Member

Create charts based on tag values from a delimited column

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:

 

bjarmuz_2-1605358968892.png

 

 

 

 

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 

1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User III
Super User III

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

vivran22
Super User I
Super User I

Hey @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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.