Forum Discussion
Cumulative Balance Name wise Total
I need result for cumulative balance as doesn't works its giving result as zero for DAX Formula, pls guide
Basically take a highest sales value and cumulative balance as mentioned on below table.
Cumulative Balance =
VAR varDate = SELECTEDVALUE('Calendar'[Date])
VAR varProduct = SELECTEDVALUE('File'[Name])
RETURN
CALCULATE(
_Measure[Sales],
'Calendar'[Date] <= varDate,
'File'[Name] = varProduct
)
result :
| Name | Cumulative Bal | Sales Total |
| Apple | 55 | 55 |
| Orange | 98 | 43 |
| Mango | 131 | 33 |
| Banana | 153 | 22 |
| Papaya | 165 | 12 |
Hi,
If it is a cumulative from largest Salest to small try this,1st. Create a measure with your sales total (it might be your measure[Sales] value already
Example:Measure = CALCULATE(SUM((File[Sales Total])))Then another measure as,
Cumulative Sum from Largest =VAR CurrentValue = [Measure]RETURNCALCULATE([Measure],FILTER(ALL(YourTable),[Measure] >= CurrentValue)
10 Replies
BmejiaSuper User
Hi,
If it is a cumulative from largest Salest to small try this,1st. Create a measure with your sales total (it might be your measure[Sales] value already
Example:Measure = CALCULATE(SUM((File[Sales Total])))Then another measure as,
Cumulative Sum from Largest =VAR CurrentValue = [Measure]RETURNCALCULATE([Measure],FILTER(ALL(YourTable),[Measure] >= CurrentValue)Hi - perfect DAX Formula, I need one more support the cumulative balance should work only v% column have value.
sample dataset:
Name Sales Total v% CUMbal Apple 55 24% 55 Orange 43 30% 98 Mango 33 131 Banana 22 22% 153 Papaya 12 80% 165 Total 165 output needs :
Name Sales Total v% CUMbal Apple 55 24% 55 Orange 43 30% 98 Banana 22 22% 120 Papaya 12 80% 132 Total 165
ryan_mayuI didn't see any date data in your result. could you pls provide some sample data?
- ryan_mayu
Anonymous
if this is your sample data, you need to have an order column.
Column = sumx(FILTER('Table','Table'[order]<=EARLIER('Table'[order])),'Table'[Sales Total])orMeasure = sumx(FILTER(all('Table'),'Table'[order]<=max('Table'[order])),'Table'[Sales Total])
v-veshwara-msftCommunity Support
Hi Anonymous ,
Thanks for using Microsoft Fabric Community and sharing the detailed requirement.Based on your scenario, since you're working with a large dataset (~7GB) and can't create a 'order' column, the best approach is to dynamically calculate the cumulative balance by descending sales, filtered to include only rows where the v% column has a value.
Please see the following DAX measure:
Cumulative Balance (With Threshold) = VAR CurrentName = SELECTEDVALUE('Datatable'[Name]) VAR CurrentSales = CALCULATE( [Total Sales], 'Datatable'[Name] = CurrentName ) VAR Threshold = SELECTEDVALUE('Sales Threshold'[Sales Threshold], 0) RETURN IF( CurrentSales > Threshold && NOT ISBLANK(SELECTEDVALUE('Datatable'[v%])), CALCULATE( [Total Sales], FILTER( ALL('Datatable'), NOT ISBLANK('Datatable'[v%]) && [Total Sales] >= CurrentSales && [Total Sales] > Threshold ) ), BLANK() )Output:
If you'd also like to apply filters like Sales > 45, add that condition inside the FILTER as shown below:
... FILTER( ALL('Datatable'), NOT ISBLANK('Datatable'[v%]) && CALCULATE(SUM('Datatable'[Sales Total])) >= CurrentSales && 'Datatable'[Sales Total] > 45 )Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.Thank you.
- v-veshwara-msft
Hi Anonymous ,
Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries.
Otherwise, feel free to reach out for further assistance.
Thank you.
- v-veshwara-msft
Hi Anonymous ,
Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.
If you're still facing issues, feel free to reach out.
Thank you.
- v-veshwara-msft
Hi Anonymous ,
We’re following up once more regarding your query. If it has been resolved, please mark the helpful reply as the Accepted Solution to assist others facing similar challenges.
If you still need assistance, please let us know.
Thank you