Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have a text column (days_group) which is a grouping of days (I.e 1-10, 11-20, 21-30) and I am trying to create a measure for each groupings so I can see the value by month on the x-axis.
However, my measure for Jan and 1-10, 11-20, 21-30 all have the same value, like it's taking the full total opposed to splitting them out by the group.
Test = Calculate ([cumulative_amount_days_group],
Test[days_group] = "1-10")
My value for Jan and days_group "1-10" is 100 but I get 400 as 11-20 = 250 and 21-30 = 50.
Hi @M_SBS_6 ,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information and description to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hello,
What is the DAX expression of the measure cumulative_amount_days_group? Are the data fields used in that measure from the same data table Test or being filtered by tha data table Test?
Hi @Zang_Mi the measure for cumulative_amount_days_group is
Calculate(
Sum( test[test_amount],
Filter(
Allselected(test),
Test[test_date] <= Test[test_date])),Group by test, test[days_group]))
Hello, I build a small sample based on your description, this is a way to calculate cumulative value per month, hope that can help you to adjust your measure.
Measure:
measure test_amount ALLSELECTED =
VAR CURRENT_MONTH = SELECTEDVALUE(test[test_date])
VAR CURRENT_DAYS_GROUP = SELECTEDVALUE(test[days_group])
RETURN CALCULATE(SUM(test[test_amount]), FILTER(ALLSELECTED(test), test[test_date] = CURRENT_MONTH && test[days_group] <= CURRENT_DAYS_GROUP))
Thank you for your suggestion but I want to be specific and show the cumulative value by each grouping. So if I take 1-10, I'd see 100 in Jan then in Feb, 200 but your example is , I think, looking at cumulative value by month opposed to the days_group?
This is what I'm hoping for:
Example looking at days_group = 1-10
Test_date. Test_amount. Cum_am
Jan. 100. 100
Feb. 50. 150
Mar. 50. 200
Apr. 200. 400
May. 100. 500
Jun. 100. 600
Jul. 200. 800
Aug. 50. 850
Sep. 50. 900
Oct. 100. 1000
Nov. 150. 1150
Dec. 50. 1200
So I would have 3 measure one for 1-10, another for 11-20 and another 21-30. Unless there's a way to have one measure and days_group added as a legend?
Hello, regarding your question, I would say it is more efficient having a unique measure for that purpose while we can use days_group as a legend. Let's assume that we create a matrix using days_group as columns (or legend in bar chart), and we can see how the cumulative sum changes each month.
Theres is a DAX expression to the solution (you can adjust it as needed).
Note: test_date_num is the a numeric value that represents month.
Cumulative sum =
VAR __START_MONTH = CALCULATE(MIN(test[test_date_num]), REMOVEFILTERS(test[test_date]))
VAR __END_MONTH = MAX(test[test_date_num])
VAR __RESULT = CALCULATE(SUM(test[test_amount]), REMOVEFILTERS(test[test_date]), test[test_date_num] >= __START_MONTH && test[test_date_num] <= __END_MONTH)
RETURN __RESULT
If this answer helps you, please give a kudo and mark it as solution 🙂.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |