Forum Discussion
Cumulative total shown the wrong values.
Hi all,
I have written a measure to calculate cumulative total.
I didn’t get expected result.
My table looks like below image :-
Based on the first column I want to show cumulative total cumulative total should be based on the 3rd column in a table.
I have write a formula but as you can see 2nd values in 2nd column is completely wrong.
here is my DAX function:
measure =
CALCULATE(
DIVIDE(
SUM(table1[viewingDuration]),
calculate(
SUM(table[viewingDuration]),
filter(ALL(table1[112]),
table[112] <> "0" &&
table[112] <> "1")))
*
distinctCOUNT(table[Id+Date])
,
ALL(table[112]),
table[112] <= max(table[112]),
filter(ALL(table[112]),
table[112] <> "0" &&
table[112] <> "1"))
Hi Anonymous ,
You can try adding an Index to the power query or use rank to add a sort.
Here are the steps you can follow：
1. In Power query. Add Column – Index Column – From 1.
2. Create measure.
total = IF( [Weighted by view time]=BLANK(),SUMX(FILTER(ALL(table1),'table1'[Index]=MAX('table1'[Index])+1),[Weighted by view time]), SUMX(FILTER(ALL(table1),'table1'[Index]<=MAX('table1'[Index])),[Weighted by view time]))1 = var _table=SUMMARIZE('table1','table1'[112],"_value",[total]) return IF(HASONEVALUE('table1'[112]),[total],SUMX(_table,[_value]))3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Hey Anonymous ,
is the column table[112] a text?
A cumulative total for a text is not really easy. Do you have the same column for the calculation also as an integer?
Can you provide an example? Then I can give you a proposal that would work.
Best regards
Denis
Yes, this column is a text column
we have only this column and we want to show it as a text. Because I don’t want to covert it
in a int.
Because int give me only single 0 or single 1 in a table and i want to show complete "000000" in a table.We have another column like searchnet.
And i have already convert it in a 000000 and 0000001 to show the x axis.
Hi Anonymous ,
You can try adding an Index to the power query or use rank to add a sort.
Here are the steps you can follow：
1. In Power query. Add Column – Index Column – From 1.
2. Create measure.
total = IF( [Weighted by view time]=BLANK(),SUMX(FILTER(ALL(table1),'table1'[Index]=MAX('table1'[Index])+1),[Weighted by view time]), SUMX(FILTER(ALL(table1),'table1'[Index]<=MAX('table1'[Index])),[Weighted by view time]))1 = var _table=SUMMARIZE('table1','table1'[112],"_value",[total]) return IF(HASONEVALUE('table1'[112]),[total],SUMX(_table,[_value]))3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
