cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ImreP
New Member

Client level grouping based on variance of data between dates

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. 

 

ImreP_0-1642681280527.png

 

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:
 
ImreP_1-1642681461642.png
 
Filtered to product 'b' and removing clients from the table:
ImreP_2-1642681861959.png

 

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

2 REPLIES 2
ImreP
New Member

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
Community Support
Community Support

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

22012501.jpg

 

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!