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 mul...
Need Help
Tips and Tricks
johnt75's avatar
johnt75
Super User
4 years ago

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

PrathSable's avatar
PrathSable
Advocate II
4 years ago

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's avatar
    johnt75
    Super User
    4 years ago

    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] ) 
    )