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

[Anonymous]

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__
AB1	JAN	11934
AB1	JAN	11934
XX1	JAN	6576
XX1	JAN	6576
XX2	JAN	4789
XX3	JAN	4789
YY2	JAN	6498
YY2	JAN	6498
YY3	JAN	3333
YY4	JAN	1242
AB1	FEB	11930
AB1	FEB	11930
XX1	FEB	6573
XX1	FEB	6573
XX2	FEB	6000
XX3	FEB	7000
YY2	FEB	3500
YY2	FEB	3500
YY3	FEB	500
YY4	FEB	100

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_
1	AB1	CAT-1	1000
2	AB1	CAT-1	1000
3	AB1	CAT-2	3000
4	XX1	CAT-2	4000
5	XX1	CAT-3	5000
6	XX1	CAT-3	5000
7	YY2	CAT-4	6000
8	YY3	CAT-4	7000

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

Or
Table3

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

Table4

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!

[Anonymous]

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,

[barritown]

@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 · Accepted solution]

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:

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian