Measure groupby text column

Question

Hi,&nbsp;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.&nbsp;&nbsp;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.&nbsp;&nbsp;Test = Calculate ([cumulative_amount_days_group],Test[days_group] = "1-10")&nbsp;My value for Jan and days_group "1-10" is 100 but I get 400 as 11-20 = 250 and 21-30 = 50.

zang_mi · Answer

Hello,&nbsp;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 · Answer

Hi Zang_Mi&nbsp;the measure for cumulative_amount_days_group is&nbsp;&nbsp;Calculate(Sum( test[test_amount],&nbsp;Filter(Allselected(test),Test[test_date] &lt;= Test[test_date])),Group by test, test[days_group]))

zang_mi · Answer

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:&nbsp;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 &amp;&amp; test[days_group] &lt;= CURRENT_DAYS_GROUP))&nbsp;

m_sbs_6 · Answer

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?&nbsp;This is what I'm hoping for:&nbsp;Example looking at days_group = 1-10&nbsp;Test_date.&nbsp; Test_amount.&nbsp; Cum_amJan.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100Feb.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 150Mar.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200Apr.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 400May.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 500Jun.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;600Jul.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;800Aug.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;850Sep.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;900Oct.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1000Nov.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 150.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1150Dec.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1200&nbsp;So I would have 3 measure one for 1-10, another for 11-20 and another 21-30.&nbsp; Unless there's a way to have one measure and days_group added as a legend?&nbsp;

zang_mi · Answer

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.&nbsp;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] &gt;= __START_MONTH &amp;&amp; test[test_date_num] &lt;= __END_MONTH)
RETURN __RESULT&nbsp;__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&nbsp;🙂.

Forum Discussion

Measure groupby text column

6 Replies

Related content

GroupBY and summarize

Different Groupby Column Names in Summarize

Calculated column groupby

Measure two columns

Expand Column After GroupBy