Forum Discussion

PrathSable's avatar
PrathSable
Advocate II
4 years ago

DAX Column for grouping values from same column

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:

 

IDNameSchemeVolume
E2334545ABC holdingAAA123232
E2334545ABC holdingBBB23424
E2334545ABC holdingCCC345345
E2334545ABC holdingDDD3454345
E2334545ABC holdingXXX3454345
E2334545ABC holdingWWW3454345
E567432ABC holdingSSA354345
E567432ABC holdingWDA546
E567432ABC holdingAAA56
E567432ABC holdingSAD46756
R57655668Real EstateGDC46766
R57655668Real EstateSSA575857
R57655668Real EstateSAD87586
R57655668Real EstateCCC8578776
R57655668Real EstateDSA544534
H7656555Limited CoRED75866
H7656555Limited CoFFF35345
H7656555Limited CoXXX3535
H7656555Limited CoWWW552
T345345334Private InstituteYYY34534
T345345334Private InstituteVBN7654
T345345334Private InstituteGDC6365
T345345334Private InstituteSAW535
T345345334Private InstituteAAA5252
T345345334Private InstituteCCC52425
T345345334Private InstituteFFF5252

 

OUTPUT:

 

Scheme combinationTotal 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

 

Member123456 amitchandak 

Need Help
Tips and Tricks

9 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    If you have something like

    Scheme CombinationScheme
    AAA-BBBAAA
    AAA-BBBBBB
    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's avatar
      PrathSable
      Advocate II
      4 years ago

      That's the main problem I do not have a scheme combination ðŸ˜ª

      That is what I am looking to create. 

      • johnt75's avatar
        johnt75
        Super User
        4 years ago

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