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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Create a visual table from many-to-many relationships connected sources and create subtotals

Hi,

I'm having some difficulty constructing a visual table, with sources with many-to-many relationships, 

Let me build the scenario,

First, I have the following fixed source table, for which I use the column "VALUE" as a reference, this table has duplicates rows,
Table1

SUB-ID_MONTH__VALUE__
AB1JAN11934
AB1JAN11934
XX1JAN6576
XX1JAN6576
XX2JAN4789
XX3JAN4789
YY2JAN6498
YY2JAN6498
YY3JAN3333
YY4JAN1242
AB1FEB11930
AB1FEB11930
XX1FEB6573
XX1FEB6573
XX2FEB6000
XX3FEB7000
YY2FEB3500
YY2FEB3500
YY3FEB500
YY4FEB100

 

Then I have this second source table, variable, which gives me the "VALUE2", this table does not contain duplicates,
Table2

ID_SUB-ID_CATEGORY_VALUE2_
1AB1CAT-11000
2AB1CAT-11000
3AB1CAT-23000
4XX1CAT-24000
5XX1CAT-35000
6XX1CAT-35000
7YY2CAT-46000
8YY3CAT-47000

 

After that, I would connect both tables 1 and 2, by the SUB-ID column, in this case, it would be a many-to-many relationship,

After combining both sources, I would like to construct a new table (Table3 - Visual), using Table2 as a base, and I would be adding the column VALUE from Table1,

But with one particular detail, I would like to insert also a column MONTH and use it as a filter to return the results based 

For example,
Table3

RodCamargoJr_1-1689791588361.png
Or
Table3

RodCamargoJr_2-1689791600989.png

 

And finally, after getting the Table3, I would construct a summarized Table4, using the column "CATEGORY" as a reference for subtotals,

Table4

RodCamargoJr_3-1689791703872.png

 

This problem seems simple by using Excel, but seems very tricky to resolve using PowerBI, 

I've tried to explain in the most simple and effective way here, I would appreciate it if anyone knows how to solve this,
Thanks!

1 ACCEPTED SOLUTION
barritown
Super User
Super User

Hi @Anonymous,

You won't mind me mentioning that your data model with this many-to-many relationship is at least nonconformist, will you? 🙂

But even with this model you can arrange those visuals (Table3 and Table4).

For Table3, you will need two measures:

MONTH Measure = MIN ( Table1[MONTH__] )

VALUE Measure = MIN ( Table1[VALUE__] )

For Table4, you will need three other measures:

VALUE SUBTOTAL_ = SUMX ( Table2, [VALUE Measure] )

VALUE2 SUBTOTAL_ = SUM ( Table2[VALUE2_] )

SUBTRACTION = [VALUE SUBTOTAL_] - [VALUE2 SUBTOTAL_]

As you can see below, the result matches your test cases:

barritown_0-1689842989971.png

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @barritown , thank you for your response, and yeah, it's a very unparticular case hehe,

Your answer has worked very well here, thanks again, but before we accept as a solution your answer,

I would like to question again:

Is there any chance for me to get the same results for Table3 and Table4 without using/making the many-to-many relationship between Table1 and Table2? 

I mean, when I change the filter of "MONTH", as being a measure and column inserted, I also change the 
rows from Table2, and would be better for me if I keep the original aspect and every ID sequence of Table2,

Thanks again,

@Anonymous,

If you drop the many-to-many relationship, you will need to modify one measure:

VALUE Measure = 
VAR CurrentSubID = MIN ( [SUB-ID_] )
RETURN MINX ( FILTER ( Table1, [SUB-ID_] = CurrentSubID ), Table1[VALUE__] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

barritown
Super User
Super User

Hi @Anonymous,

You won't mind me mentioning that your data model with this many-to-many relationship is at least nonconformist, will you? 🙂

But even with this model you can arrange those visuals (Table3 and Table4).

For Table3, you will need two measures:

MONTH Measure = MIN ( Table1[MONTH__] )

VALUE Measure = MIN ( Table1[VALUE__] )

For Table4, you will need three other measures:

VALUE SUBTOTAL_ = SUMX ( Table2, [VALUE Measure] )

VALUE2 SUBTOTAL_ = SUM ( Table2[VALUE2_] )

SUBTRACTION = [VALUE SUBTOTAL_] - [VALUE2 SUBTOTAL_]

As you can see below, the result matches your test cases:

barritown_0-1689842989971.png

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.