Client level grouping based on variance of data between dates

[ImreP]

Hi,

I'd like to create a chart where my total value movement is separated by those clients who had an increase in total, and those who had a decrease in total. If I filter any other field, the grouping should only include the filtered data. So it is possible that in total client A had an increase, but if I filter by product that client had a decrease in that product between two periods. 

The final visual is a stacked column, with a "Client level increase", and a "Client level decrease" measure where the sum of the two is the net total change of a given value.

The problem seems to be that when there was a record in period T0, but there is no record in period T, DAX does not compute the difference between the two periods when I group by clients. 

Here is an example:

The data set to be evaluated. Product B is missing from 2021.12.31. in case of client 1. 

Here is how I calculate T0 and client level decrease, with two different formulas. (the increase logic is the same)

Value measure = SUM(data[value])

value measure T0 = CALCULATE([Value measure],DATEADD('Reporting date'[DateKey],-1,MONTH))

Value decrease client level =

sumx

(

filter

(

GROUPBY(data,data[client]),

[Value measure change]<=0

),

[Value measure change]

)

Value decrease client level 2 =

var v_table_sum = SUMMARIZE(data,data[client])

var v_table_Sum2 = ADDCOLUMNS(v_table_sum,"Closing balance change sum",[Value measure change])

var v_table_filter = FILTER(v_table_sum2,[Closing balance change sum]<=0)

var result = sumx(v_table_filter,[Value measure change])

return result

Here are the resulting tables with the measures:

Filtered to product 'b' and removing clients from the table:

The problem is product 'b' for client '1': Value decrease client level is not computed. I guess it is due to the handling of blanks in DAX but can't figure how to modify my code. 

Any ideas?

Thanks. 

Imre

[ImreP]

Hi @v-jingzhang ,

Thanks for your response. 

I have two remarks:

• I have not mentioned in the original post but solving this issue with only measures is preferred as I am accessing the data via Live connection so I do not have the flexibility to add new tables or columns. I could request a change in the data model but I might not get the approval to do so.
• There are 10 dimensions based on which users can filter the page. This means I would need 10 dimension tables. And more if we expand the filter options. 

Imre

[v-jingzhang]

Hi @ImreP 

You need to add three Dim tables for products, clients and dates separately. Build relationships from Dim tables to the fact data table. And use columns from Dim tables in filters/slicers and measures. This could avoid data missing when there is no data in some period in the fact table. 

Then you can try these measures:

Value decrease client level 3 = 
var v_table_sum = SUMMARIZECOLUMNS(Clients[client],Products[product],"Closing balance change sum",[Value measure change])
var v_table_filter = FILTER(v_table_sum,[Closing balance change sum]<=0)
var result = sumx(v_table_filter,[Closing balance change sum])
return result

Value increase client level 3 = 
var v_table_sum = SUMMARIZECOLUMNS(Clients[client],Products[product],"Closing balance change sum",[Value measure change])
var v_table_filter = FILTER(v_table_sum,[Closing balance change sum]>0)
var result = sumx(v_table_filter,[Closing balance change sum])
return result

You can download the attachment to see details. If this is not what you want, let me know the expected output so I can modify it then. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.