DAX Column for grouping values from same column

[PrathSable]

Hi,

Need help on getting a tricky problem resolved on the data - 

I have the below table combining 4 columns - ID, Name, Scheme & Volume

What I need to do is from the Scheme which has multiple schemes, I need to create a calculated column that will automatically group the schemes and sum up the cost - it might be a permutation/combination methodology, but I am trying to work how I can achieve this - I tried summarise, earlier, Treatas etc and 

INPUT:

ID	Name	Scheme	Volume
E2334545	ABC holding	AAA	123232
E2334545	ABC holding	BBB	23424
E2334545	ABC holding	CCC	345345
E2334545	ABC holding	DDD	3454345
E2334545	ABC holding	XXX	3454345
E2334545	ABC holding	WWW	3454345
E567432	ABC holding	SSA	354345
E567432	ABC holding	WDA	546
E567432	ABC holding	AAA	56
E567432	ABC holding	SAD	46756
R57655668	Real Estate	GDC	46766
R57655668	Real Estate	SSA	575857
R57655668	Real Estate	SAD	87586
R57655668	Real Estate	CCC	8578776
R57655668	Real Estate	DSA	544534
H7656555	Limited Co	RED	75866
H7656555	Limited Co	FFF	35345
H7656555	Limited Co	XXX	3535
H7656555	Limited Co	WWW	552
T345345334	Private Institute	YYY	34534
T345345334	Private Institute	VBN	7654
T345345334	Private Institute	GDC	6365
T345345334	Private Institute	SAW	535
T345345334	Private Institute	AAA	5252
T345345334	Private Institute	CCC	52425
T345345334	Private Institute	FFF	5252

OUTPUT:

Scheme combination	Total Volume
AAA-BBB	1,57,216
SSA-AAA	10,58,742
WWW-GDC	35,13,280

So basically it should group the combinations from the Scheme & combine the volumes together

Is there any way to achieve this? I am really stuck on this session & would really appreciate any help that comes through.

Thanks

Prath

@Admin @amitchandak 

[johnt75]

If you have something like

Scheme Combination	Scheme
AAA-BBB	AAA
AAA-BBB	BBB
WWW-GDC	WWW
WWW-GDC	GDC

with a many-to-many relationship from this table to your scheme table, then your total volume measure could be something like

Total Volume = SUMX( 'Scheme Combination Table',
    SUMX( RELATEDTABLE( 'Schemes' ), 'Schemes'[Volume] )
)

[PrathSable]

That's the main problem I do not have a scheme combination 😪

That is what I am looking to create. 

[johnt75]

Are you looking to combine every scheme with every other scheme ?

[PrathSable]

What I am trying to do is work out whats the combinations of the schemes and then which accounts will fall under that combination and what volumes can I expect if I combine both those schemes together to sell to the account. 

[johnt75]

what's the logic behind a scheme combination? what determines whether a scheme combination is valid or real ?

[PrathSable]

Exactly, what I need to basically do is - try to find out based on how I combine the schemes which combination suits best for a customer from where I can get a good volume (here volume is the revenue) Like whether for a customer ABC holding which scheme combination works (AAA-BBB or XXX-WWW) And vice verse if I combine the schemes SAD-YYY so I have 2 customers CCC-YYY so I have 2 customers there but that combination gives me highest revenue out. 

It's similar to =+SUMIFS that excel uses

Also, I can bring n same column twice in there like Scheme 1, Scheme 2 to figure out whether that can work.

I know this is a little tricky. Thank you very much in advance for understanding this.

[johnt75]

You could generate a table with every possible combination with something like

Combination Table =
ADDCOLUMNS( FILTER(
    GENERATE( VALUES( 'Table'[Scheme] ),
         SELECTCOLUMNS( CALCULATETABLE( VALUES( 'Table'[Scheme] ), ALL() ),
            "2nd scheme", 'Table'[Scheme]
         )
    ),
    [2nd scheme] <> [Scheme]
),
"Combination name", [Scheme] & "-" & [2nd scheme]
)

but bear in mind that this will include both "AAA-BBB" and "BBB-AAA" variations of the combination.

Once you have this table in place create relationships from both scheme columns to 'Table'[Scheme].

Now you can create measures to get total values for a scheme combination with something like

Scheme Combination Total =
CALCULATE( SUM( 'Table'[Volume] ), USERELATIONSHIP( 'Table'[Scheme], 'Combination Table'[Scheme]) )
+ CALCULATE ( 
    SUM( 'Table'[Volume] ), USERELATIONSHIP( 'Table'[Scheme], 'Combination Table'[2nd scheme] ) 
)

[PrathSable]

Tried that doesn't work, can you share a example of it please?

[johnt75]

How about a mapping table so that each Scheme Combination had a row for each Scheme, link the Scheme column from the new table to the Scheme column in your data in a many-to-many relationship. I think that's the only way to define which schemes fit into which combinations