Forum Discussion

rymerco's avatar
rymerco
Frequent Visitor
8 years ago
Solved

Distinct Subtotals by Dynamic Time Intelligent Occurrence of Combinations

Greetings to Everyone and Happy New Year 2018!

 

I have a unique problem that I have not been able to solve in DAX without a circumvented and inefficient method.  The problem I am having is that the dax measure needs to be a dynamic, time intelligent accrual of cumulative totals within date segments.

 

 

Here is an example where the first image is MASTERDATASET:

 

Date Range = Dynamic - but for this example, the time slicer is set for 1/1/17 to 1/11/17

 

I want the subtotals of Value1 within that date range.

 

The wrinkle is in how the subtotals are aggregated.

 

The result comes from:

Identifier1 AA has a combination with Identifier2 of

AA BB, AA DD, AA CC.  So I want to total all of Value1 in the instances where BB, CC, DD are Identifier1 in the date range.

 

 

Measure = Subtotal for AA = sum Value1 where BB, CC, DD is Identifer1 = 8 + 5 + 7 + 8 = 28

 

But I want this for all distinct values in Identifier1.  End result in the formula would subtotal all for AA, BB, CC, DD

 

AA = (BB AA) 8 + (CC BB) 5 + (CC AA) 8 + (DD AA) 7 = 28

description: because AA has 3 occurences in Identifier1 column with BB in Identifier2 [AA BB on 1/1/17], CC in Identifier2 [AA CC on 1/11/17], and DD in Identifier2 [AA DD on 1/5/17]. therefore, I want the total of Value1 in all occurences of BB, CC, DD as it occurs in Identifier1.

 

...furthermore, in PowerBI I would be able to also have it specify...

 

BB = (AA BB) 10 + (AA DD) 6 + (AA CC) 12 = 28

description: because BB has only 1 occurence in Identifier1 column with AA in Identifier2 [BB AA on 1/2/17].  Yet, AA has 3 occurences in Identifier1, as noted above.  Therefore, I want all of the totals where AA is in Identifier1.

 

CC = (BB AA) 8 + (AA BB) 10 + (AA DD) 6 + (AA CC) 12 = 36

description: CC has 2 occurences in Identifier1 with BB in Identifier2 [CC BB on 1/5/17] and AA in Identifier2 [CC AA on 1/2/17].

 

...and so on...

 

DD = (AA BB) 10 + (AA DD) 6 + (AA CC) 12 = 28

description: again, because there is only 1 occurence of DD in Identifier1 with the combo of AA in Identifier2 [DD AA on 1/8/17].

 

The measure would then produce the following visualization table dynamically in Power BI:

 

 

 

 

 

 

I would then want the numbers to dynamically change if I changed the date range to 1/1/17 to 1/2/17.

AA BB 1/1/17

BB AA 1/2/17

CC AA 1/2/17

 

The measure would produce the following visualization dynamically in Power BI in table format:

 

 

 

 

 

 

I hope this is not as confusing as I feel it reads.  This seems like it would be very simple to generate, but I cannot get it to work.

 

I would really appreciate the forum's assistance to help me crack the code here.

  • Hi rymerco,

     

    Based on my test, the formula below show work in your scenario. :smileyhappy:

    Measure = 
    VAR currentIdentifier1 =
        FIRSTNONBLANK ( Table1[Identifier1], 1 )
    RETURN
        SUMX (
            FILTER (
                ALLSELECTED ( Table1 ),
                CONTAINS (
                    FILTER ( ALLSELECTED ( Table1 ), Table1[Identifier1] = currentIdentifier1 ),
                    Table1[Identifier2], Table1[Identifier1]
                )
            ),
            Table1[Value1]
        )
    

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee

    Hi rymerco,

     

    Based on my test, the formula below show work in your scenario. :smileyhappy:

    Measure = 
    VAR currentIdentifier1 =
        FIRSTNONBLANK ( Table1[Identifier1], 1 )
    RETURN
        SUMX (
            FILTER (
                ALLSELECTED ( Table1 ),
                CONTAINS (
                    FILTER ( ALLSELECTED ( Table1 ), Table1[Identifier1] = currentIdentifier1 ),
                    Table1[Identifier2], Table1[Identifier1]
                )
            ),
            Table1[Value1]
        )
    

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

    • rymerco's avatar
      rymerco
      Frequent Visitor

      This appears to be the fix.  Thank you for your assistance.  Much appreciated as I was spinning in circles trying to find a more efficient solution.