Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
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
Hi @v-jingzhang ,
Thanks for your response.
I have two remarks:
Imre
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |