Measure groupby text column

[M_SBS_6]

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.

[v-shex-msft]

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

[Zang_Mi]

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?

[M_SBS_6]

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]))

[Zang_Mi]

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))

[M_SBS_6]

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? 

[Zang_Mi]

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

• __START_MONTH. The first month selected per each days_group. In our case, it is always the Jan.
• __END_MONTH. The current month used as axis, it can be January or any month.
• __RESULT. Calculate per each month displayed in the axis, the cumulative sum from the first month to the current month.

If this answer helps you, please give a kudo and mark it as solution 🙂.