Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to calculate cumulative Total and % in DAX?

Hi

I need Help

How to calculate cumulative Total and % Total in DAX for table below ?

 

Regards,

 

cu.PNG

 

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

There's no Date table. 

 

Why are you writing "'Date'[Date] <= MAX ( 'Date'[Date] )" ? And there's one more brackets which need to be deleted.

 

Check this: https://1drv.ms/u/s!AlqSnZZUVHmshXHPvHYIWTk3YaUL

 

Thanks,
Xi Jin.

View solution in original post

14 REPLIES 14
Ashish_Mathur
Super User
Super User

Hi,

 

Share the PBI file and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Here's the % Total measure:

 

[% Total] =
VAR Numerator =
    CALCULATE ( [Total Customer Deposits measure] )
VAR Denominator =
    CALCULATE ( 
        [Total Customer Deposits measure], 
        ALL ( table[customer_name_e] ) 
    )
RETURN
    DIVIDE ( Numerator, Denominator )

Cumulative total is a little difficult, we'd need to see the table structure that you have to better understand how to rank / sort the customer_name_e column to include all customers upto and including the current customer.  It's not impossible, but we need more information first.

Anonymous
Not applicable

Hi  ChrisHaas,

thanks for your answer.

 For Cumulative total, I sort  table  by   total customer deposits column

 

Hi @Anonymous,

 

To calculate the cumulative total based on the order of total customer deposits column. You can refer to following method:

 

Create a calculated column with below expression:

 

Cumu Total =
CALCULATE (
    SUM ( 'Custom'[Custom deposits] ),
    FILTER (
        ALL ( 'Custom' ),
        'Custom'[Custom deposits] <= EARLIER ( 'Custom'[Custom deposits] )
    )
)

 

5.PNG

 

Thanks,
Xi Jin.

Anonymous
Not applicable

Thanks Xi Jin,

 

I trace  your  steps, but I find Error.

Kindly find image  below error.PNG

 

 

what is  the solve?

 

Regards,

Mohammed

Hi @Anonymous,

 

Earlier() function is used in Calculated Column not a Measure. Please create a New Column instead of a New Measure.

 

If you want to use Measure, try this:

 

Cumulative =
CALCULATE (
    SUM ( 'Custom'[Custom deposits] ),
    FILTER (
        ALL ( 'Custom' ),
        'Custom'[Custom deposits] <= MAX ( 'Custom'[Custom deposits] )
    )
)

 

Thanks,
Xi Jin.

Anonymous
Not applicable

Hi Xi Jin.,

 

the same error, but  now  i try  in small case with student test table but, the data retrive in cumulative  column is faluse, after that i change the  function with  ear.JPGtest max.JPG

kindly find bewlo snapshots

 

 

Hi @Anonymous,

 

First, did you mean even with a Calculated Column, above error still shown up? If so, could you please share us your pbix file with One Drive or Google Drive if possible? So that I can dig deeper and find out the issue.

 

Then for the student test, the first screenshot should be right. What's wrong with it?

 

As I said before, EARLIER() function is used in calculated column. Also use "'Test Student'[mark]<=MAX('Test Student'[mark])" in a Measure not in a calculated column. This is different, don't mess up with them.

 

Thanks,
Xi Jin.

 

Anonymous
Not applicable

thanks Xi Jin,

 

I  share pbix file  here,   the  report  inside page 2 ,kindly trace what is  the error with me and thanks advanve 

Regards 

Hi @Anonymous,

 

There's no Date table. 

 

Why are you writing "'Date'[Date] <= MAX ( 'Date'[Date] )" ? And there's one more brackets which need to be deleted.

 

Check this: https://1drv.ms/u/s!AlqSnZZUVHmshXHPvHYIWTk3YaUL

 

Thanks,
Xi Jin.

Anonymous
Not applicable

thanks

 

what do you mean Date table? and how I can create it ?

and for some brackets inside report, the report for test only , after that I will change the main report depended on your answer,

 

but now I dn't have table with name "Date table" how I can do that, your pbix file show right resulte without i saw Date table and the DAX format not use this sentaxy writing "'Date'[Date] <= MAX ( 'Date'[Date] )" ?

kindly find below image for your new pbix file 

fix.JPG

 

 

Hi @Anonymous,

 

Have you ever checked your original shared report? The expression for Cumulative is:

 

Cumulative = 
CALCULATE (
    SUM ( 'Test Student'[mark] ),
    FILTER (
        ALL ( 'Test Student' ),
      'Date'[Date] <= MAX ( 'Date'[Date] )
    
)))

That's why I'm saying there's a Date table in the formula. However no Date table in your report. 

 

And for my new pbix file, I have modified the expression. For sure you can't see the wrong expression.

 

Thanks,
Xi Jin.

Anonymous
Not applicable

Thanks,


Tell me how i can create date table , because I don't have any Date In my orignal reoprt

if you can support me with snapshot for the report in your PC beacuse I open your pbix file I don't find any change in the report but, the resulte is right.!!

Hi @Anonymous,

 

Simply create a Calendar table:

 

Modeling -> New Table with CALENDAR() function:

 

Calendar = CALENDAR(DATE(2017,01,01),DATE(2018,12,31 ))

 

Thanks,
Xi Jin.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.