Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I need Help
How to calculate cumulative Total and % Total in DAX for table below ?
Regards,
Solved! Go to 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.
Hi,
Share the PBI file and show the expected result.
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.
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] ) ) )
Thanks,
Xi Jin.
Thanks Xi Jin,
I trace your steps, but I find Error.
Kindly find image below
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.
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
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.
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.
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
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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |