Forum Discussion
Handle Cumulative sums coming from database
How to handle cumulative sums coming from DB at key column(Prod ID) level, But i want to aggregate at Product dimension ( Category, name) in power bi .
Hi Anonymous
The [month] you need to change it type to date will contribute to calculation.
Then create a new table named 'output in power bi' via the following expression:
output in power bi = SUMMARIZE( 'input from DB', 'input from DB'[country], 'input from DB'[month], "cnt", SUM( 'input from DB'[count] ) )And add new column in the 'output in power bi'
3mnth running total = SUMX( FILTER( 'output in power bi', [month] >= DATEADD( 'output in power bi'[month], -3, MONTH ) && [month] <= EARLIER( 'output in power bi'[month] ) && [country] = EARLIER( 'output in power bi'[country] ) ), [cnt] )the result:
I put the pbix file in the attachment you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
I think we should create a new column that is not cumulative and use that
new column =
var _max = maxx(filter(Table, [Product] < earlier([Product])) ,[Product])
// or use this on value var _max = maxx(filter(Table, [value] < earlier([value])) ,[Product])
return
[Value] - maxx(filter(Table, [Product] =_max) ,[Product])
input from DB:
month country Product_Id count 3mnth Running total 202011 A P1 10 10 202012 A P1 20 30 202011 A P2 30 30 202012 A P2 12 42 202101 A P2 20 62 202011 B P2 15 15 202012 B P2 10 25 202101 B P2 3 28 output in power bi:
country_desc mnth cnt 3mnth running total A_desc 202011 40 40 A_desc 202012 42 82 A_desc 202101 20 102 Note: our assumption is to create cumulative sums in DB to improve performace of power bi by reducing number of measures.
Hi Anonymous
The [month] you need to change it type to date will contribute to calculation.
Then create a new table named 'output in power bi' via the following expression:
output in power bi = SUMMARIZE( 'input from DB', 'input from DB'[country], 'input from DB'[month], "cnt", SUM( 'input from DB'[count] ) )And add new column in the 'output in power bi'
3mnth running total = SUMX( FILTER( 'output in power bi', [month] >= DATEADD( 'output in power bi'[month], -3, MONTH ) && [month] <= EARLIER( 'output in power bi'[month] ) && [country] = EARLIER( 'output in power bi'[country] ) ), [cnt] )the result:
I put the pbix file in the attachment you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
