Forum Discussion
Helper IIMonthly cumulative values for pivoted data?
Hi
A colleague is working on some monthly numbers, for different categories, and she wants to calculate and graph the running sum by month too.
Her data are already pivoted in Excel, and she added new columns in Excel to calculate the running sum. She can do that in Power BI too.
Is there any benefit to do it in Excel vs Power BI?
In terms of graphing, I have not found a way to do that other than unpivot the running sum in the Query Editor first, but is there a way to plot the pivoted running sum by months?
Also, in terms of calculating the running sum, I saw some examples that other users use the unpivoted data and a measure to calculate the running sum. Is that a better approach than using the pivot monthly data to calculate the running sum?
Thank you.
Hi Dimani ,,
The approach
"Also, in terms of calculating the running sum, I saw some examples that other users use the unpivoted data and a measure to calculate the running sum. Is that a better approach than using the pivot monthly data to calculate the running sum?"
is the best.
Cheers
CheenuSing
Hi Dimani
Just as CheenuSing said that you don't need to pivot the data in power bi.
Different with Excel, Power bi's calculations are primarily column-based, which results in less flexibility than Excel in some ways. Excel allows for quick adjustments and manual interventions if needed, but it might require more manual effort to create complex visualizations. And Power BI offers advanced visualization options and interactive dashboards, making it easier to explore and present data.
In Power BI, you can plot the pivoted running sum by months without unpivoting the data. A measure can easily solve the problem without taking up additional system storage space.
And if you want to get a pivot table, maybe you can try the matrix visual:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Dimani ,,
The approach
"Also, in terms of calculating the running sum, I saw some examples that other users use the unpivoted data and a measure to calculate the running sum. Is that a better approach than using the pivot monthly data to calculate the running sum?"
is the best.
Cheers
CheenuSing
DimaniThanks for your information, CheenuSing.
Hi Dimani
Just as CheenuSing said that you don't need to pivot the data in power bi.
Different with Excel, Power bi's calculations are primarily column-based, which results in less flexibility than Excel in some ways. Excel allows for quick adjustments and manual interventions if needed, but it might require more manual effort to create complex visualizations. And Power BI offers advanced visualization options and interactive dashboards, making it easier to explore and present data.
In Power BI, you can plot the pivoted running sum by months without unpivoting the data. A measure can easily solve the problem without taking up additional system storage space.
And if you want to get a pivot table, maybe you can try the matrix visual:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- Dimani
Thank you for your information, Zhengdong.