Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hello all,
I have a problem and i can't find any solution.
I have one running total measure (this one is ok):
_YTD CASH 512 = IF ( MIN ('Calendar'[Date] ) <= CALCULATE ( MAX ( 'G_L Entry'[Posting Date]); ALL ('G_L Entry') ); CALCULATE (SUM('G_L Entry'[Amount]); FILTER('G_L Account Category';'G_L Account Category'[Description]="Cash"); FILTER ( ALL ('Calendar'[Date] ); 'Calendar'[Date] <= MAX('Calendar'[Date]) )))
And another measure that I can't find out how to sum up the way i want (this one below is wrong) :
_YTD CASH 512<0 test2 = IF([_YTD CASH 512]<0;0;[_YTD CASH 512])
I would like the measure to show results only when sum of running total is > to 0 but calculated by [No].
In other words, the measure must calculate running total normaly but when Running Total for a date < 0, then value for this date =0. This should be calculated independently for each G_L Account [No_] and then sum all of the value (see below)
Additionnal info : G_L Account [No_] is subcategory of G_L Account Category'[Description]="Cash"
Let me know if i'm not clear (I feel i'm not....)
And i thanks A LOT to the people who are kind to help...
Hi Samlebijoutier,
Below is my design, I don't know your sample, below is my sample
idmonthstaamount
1 | 1 | 52000 | 10 |
1 | 2 | 52000 | 0 |
1 | 3 | 52000 | 100 |
1 | 4 | 52000 | 200 |
1 | 1 | 51000 | -1100 |
1 | 2 | 51000 | 100 |
1 | 3 | 51000 | -10 |
1 | 4 | 51000 | 20 |
2 | 1 | 52000 | 20 |
2 | 2 | 52000 | 0 |
2 | 3 | 52000 | 0 |
2 | 4 | 52000 | 10 |
2 | 1 | 51000 | 0 |
2 | 2 | 51000 | -10 |
2 | 3 | 51000 | -10 |
2 | 4 | 51000 | 30 |
I saw you calculate sum based on another measure, here I use a simple meausre,Measure 2 = SUM('Table'[amount]), you could replace this by your measure(_YTD CASH 512) to see whether it works or not
Then I create another measure
Measure 3 = SUMX ( FILTER ( SUMMARIZE ( 'Table', 'Table'[id], 'Table'[month], 'Table'[sta], "tempsum", IF ( [Measure 2] > 0, [Measure 2], 0 ) ), 'Table'[id] = MIN ( 'Table'[id] ) && 'Table'[month] = MIN ( 'Table'[month] ) ), [tempsum] )
Measure 4 = IF ( HASONEVALUE ( 'Table'[month] ), [Measure 3], SUMX ( ( 'Table' ), [Measure 3] ) )
Then use measure4 in table like below
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Zoe,
Sorry but it does not work.
I can't find a solution.
Anybody help is welcome.
Might be worth providing your report/data set so people can have a play with it. Obviously, remove any confidential information/data.
Hi Samleijoutier,
Based on your sample, you could try below measures(I create a column in entry
)
Measure 2 = SUMX ( SUMMARIZE ( entry, entry[G_L Account No_], "tempsum", IF ( [_YTD CASH 512] > 0, [_YTD CASH 512], 0 ) ), [tempsum] )
Measure 3 = IF ( HASONEVALUE ( entry[month] ), [Measure 2], SUMX ( SUMMARIZE ( entry, entry[G_L Account No_], entry[month], "a", [Measure 2] ), [a] ) )
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hello all, @Anonymous , @dax
Thank you Zoe but i can't apply your solution to my datas. I unfortunately made a mistake when i tried to simplify the dataset and remove confidential information. The Running total Measure was wrong.
The good sample is here (i kept your measures of your last answer)
I've added some explanation inside.
Thank a lot.
User | Count |
---|---|
93 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |