Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
2 years ago

Measure groupby text column

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.    ...
Need Help
Zang_Mi's avatar
Zang_Mi
Resolver II
2 years ago

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's avatar
M_SBS_6
Helper V
2 years ago

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's avatar
    Zang_Mi
    Resolver II
    2 years ago

    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's avatar
      M_SBS_6
      Helper V
      2 years ago

      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's avatar
        Zang_Mi
        Resolver II
        2 years ago

        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 ğŸ™‚.