Forum Discussion

Kazu's avatar
Kazu
Icon for Helper II rankHelper II
11 months ago
Solved

Crossjoin and then summarize

I want to 1) crossjoin two tables, 2)do some calculation, and finally 3) summarize/aggregate. I was able to do 1 and 2 but cannot the summary at 3. When I tried to SUMMARIZE or SUMMARIZECOLUMNS, it ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    11 months ago

    Hi,

    I am not sure if I understood your question correctly, and I cannot know what is the expected result looks like.

    Please check the below picture and the attached pbix file, if I missed something.

     

     

     

    EVALUATE
VAR SHIFT  = 2
VAR REPEAT = 3

// Create a temporary table crossjoining Base Table with Base Table
VAR TBL1 = ALL(BaseTable)
VAR TBL2 = SELECTCOLUMNS(
    ALL(BaseTable),
    "Index2", BaseTable[Index],
    "Value2", BaseTable[Value]
)
VAR TBL3 = ADDCOLUMNS(
    CROSSJOIN(TBL1, TBL2),
    "Value3", [Value2] * IF([Index2] >= BaseTable[Index]-SHIFT*(REPEAT-1) && [Index2] <= BaseTable[Index] && MOD(BaseTable[Index],SHIFT) = MOD([Index2], SHIFT), 1, BLANK())
)
RETURN 
SUMMARIZE(
ADDCOLUMNS(TBL3, "@Value3Sum", sumx(FILTER(TBL3, BaseTable[Index] = EARLIER(BaseTable[Index])), [Value3])), BaseTable[Index], [@Value3Sum])
ORDER BY BaseTable[Index]

     

pbix1's avatar
pbix1
Icon for Resolver II rankResolver II
10 months ago

Hi Kazu

 

I have done something similar to this, but with the summarisations done before the calculations. Obviously replace the tables, calculations etc. with your own. However, most of it will work using the generic naming. Hopefully this may help you. The DAX below is to create a calculated table.

 

PS - if anyone reading this has any ideas on how to improve this or make it more efficient, I would be interested to know. Thanks.

 

Table =
VAR SP01 = SELECTCOLUMNS('Table_A',"Week_Code_A",'Table_A'[Week_Code_A],"Data_A",'Table_A'[Data_Value_A])
VAR SP02 = SELECTCOLUMNS(FILTER('Table_B','Table_B'[Flag_B]="B"),"Week_Code_B", 'Table_B'[Week_Code_B],"Data_B",CONVERT('Table_B'[Data_Value_B],DOUBLE))
VAR SP03 = SELECTCOLUMNS(FILTER('Table_C','Table_C'[Flag_C]="C"),"Week_Code_C", 'Table_C'[Week_Code_C],"Data_C",CONVERT('Table_C'[Data_Value_C],DOUBLE))
VAR SP04 = GROUPBY(SP01,[Week_Code_A],"Total_A",SUMX(CURRENTGROUP(),[Data_A]))
VAR SP05 = GROUPBY(SP02,[Week_Code_B],"Total_B",SUMX(CURRENTGROUP(),[Data_B]))
VAR SP06 = GROUPBY(SP03,[Week_Code_C],"Total_C",SUMX(CURRENTGROUP(),[Data_C]))
VAR SP07 = CROSSJOIN(SP04,SP05,SP06)
VAR SP08 = FILTER(SP07,AND([Week_Code_A]=[Week_Code_B],[Week_Code_B]=[Week_Code_C]))
VAR SP09 = ADDCOLUMNS(SP08,"Calculation",IF([Total_A]=0,0,DIVIDE([Total_A]-[Total_B]-[Total_C],[Total_A])))
VAR SP10 = SELECTCOLUMNS(SP09,"Week_Code",[Week_Code_A],"Result_A",[Total_A],"Result_B",[Total_B],"Result_C",[Total_C],"Percentage",FIXED([Calculation],10))

RETURN
CALCULATETABLE(SP10)