Forum Discussion
Matrix - row total formula
Hello,
I have line items (rows) in a matrix and I would need the Total to be calculated differently for different line Items. For ex, it needs to add all monthly values for one and avg for others. This is a P&L sheet.
How can i achieve this? Is there any other visualization for this instead of Matrix?
Below is a screen shot of the same. Kindly advise.
Thank you,
Harisha
Bhavesh and Angelia, thanks for your response.
For now, i got around the problem by getting the Yearly totals from the source itself. This is being calculated in the source data so i am able to pull it in.
But surely would like to know how such requirements (P&L items) are handled in Power BI. Looks like there is better control in excel as we can manipulate (even in terms of formatting) at cell level once the pivot table is placed on the sheet.
Thank you,
Harisha
6 Replies
Have you ever used a pivot table for this? If not possible, it is unlikely that row total can be done in Power BI desktop.
Thanks for your reply.
By pivot, you mean in excel right? Our customer has requirement for both excel and PowerBI. Not sure how to achieve this for Matrix in Power BI.
Thank You,
Harisha
Hi Harisha,
For the exact solution of your problem, please visit this blog post from Rob Collie which will give you more ideas on creating such grand totals.
hmokkapatiIn Power BI Desktop, the data field you put in Values pane will be aggregated in “Total” column. We can only specify one aggregation type. It’s not possible to specify a dynamic aggregation type for same field.
Best Regards,
AngeliaBhavesh and Angelia, thanks for your response.
For now, i got around the problem by getting the Yearly totals from the source itself. This is being calculated in the source data so i am able to pull it in.
But surely would like to know how such requirements (P&L items) are handled in Power BI. Looks like there is better control in excel as we can manipulate (even in terms of formatting) at cell level once the pivot table is placed on the sheet.
Thank you,
Harisha
