I use power bi with live connection to SQL analysis server so I can not create tables, relationship etc. I have only access to creating measures.
If I have different types of revenues such as revenues x, revenues y, and other revenues, can I create a roll up category called revenue so I can sum all revenues using a matrix. I do not want to have a measure since this will create a new column and I already have budget and actual column to sum the revenue on both.
Actual. Budget. Actg to be created
Revenue x. 100. 130. Revenue
Revenue y. 300. 280. Revenue
Other Rev. 10. 8. Revenue
First when you say different types of revenues what does this actually mean? Are they just in different columns? If so using a measure would be a simple solution here.
Also you mention that creating a measure would create another column? Measure don’t create additional columns so this can’t be correct. Do you instead mean a calculated column? I would always use measures here instead, they are far more optimal for what it sounds like you are requiring.
This is really all I’m understanding right now. For additional assistance maybe some images would be helpful.
Thanks for your reply. Different revenue categories are in rows not column. This is the same as an income statement where you have rows and we need to create a subtotal.
If the connection is not live we can simply create another table to roll up all revenues and then subtotal on them. Now with live connection I can not create a table so we just need to create a measure to roll up different revenue items so we can create a subtotal. Please let me know whether you still need a screen shot.
Here you go, an example of the data. I need to create a Dax measure that will look into the field account to see all revenues rows and map it to be Revenue only, so I can report on the new field and get a sum in online.
Firstly I would avoid live connections if you can. You will run into so many problems like this you can really fix in Power BI. With a live connection you can only really use 10% of what is available in Power BI.
This can be easily solve in the table itself by added that column.
There’s no effective way to roll up financial statements other than the technique covered in the below video quite in depth.
The reality is here I don’t believe you can get exactly how you want without being able to build a model around your data.
You can’t do this with live connections.
Highly recommend running through the above videos, they lay out basically everything you need to know around creating effective financial reporting in Power BI