Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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
Super User

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



vivran22
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.