Profit and Loss

[StuBPortcullis]

Hello all

New to this forum and indeed DAX & Power BI.

Have managed to build a reliable Excel model with Dax Time Intelligence Functionality, however when I import to Power BI, it will now show any P&L line items for calculated lines such as Gross Profit, EBITDA etc, when I sort the header by sort order numbers in a separate table. If I remember back to the Excel Pivot Table Model, I seem to recall that it also didn't sort in the correct order, but I had the ability to drag and drop into the correct sequence. I've tried a custom sort order, and the same thing happens, in that the calculated rows dissapear. Please see the below example of my P&L.

Any help you could give would be greatly appreciated, I've got a nagging feeling that it's related to the DAX measures that I've used to calculate the calculated rows.

[richbenmintz]

Hi @StuBPortcullis,

you need to have a sort by column in your table that defines your P&L header and detail rows. you then assign the sort by column in Power BI Desktop

Thanks,

[StuBPortcullis]

Thanks for the quick reply. unfortunately, I've tried this and a custom sort format, i.e Revenues =1, COS = 2 etc, but when sorting, the calculated rows disappear.

[richbenmintz]

Hi @StuBPortcullis ,

Can you provide the pbix?

[StuBPortcullis]

I can, but how do I go about doing that? Can you tell I'm a newbie?

[richbenmintz]

Google Drive or One Drive

[aj1973]

Hi @StuBPortcullis 

Good thing you have your DAX measures for each row in the Matrix. To sort by your measures you need to store those measures into a calculated table as One column for Index and another column for your measures. use if condition to allign the measures in front of each index. Once done you can sort your measures by the Index.

I myself made a P&L report, however I used different approach. Check it out if you want

Microsoft Power BI

[StuBPortcullis]

Hi Amine

Good work, I looked at your reports and they looked good.

I'm not sure that I totally understand the Index suggestion. I have 13 measures setup for Period actuals, PY same period actuals and Budget. I also have measures for YTD actuals, YTD PY and YTD Budget, so a total of 78 measures. On a matrix, I would like to see the P&L header categories on the left, with these columns to the right of the header.

[aj1973]

As I told you in my Report I used different approach. But for you do these steps

1- Add new Table and generate a series of 13 numbers = to you number of measures

Table of measures = GENERATESERIES(1 , 13 , 1)

2- Then Add a column to your table where you use if to store your measures according to order you like

3- then you can use or order your measures in the visual following the index order

[StuBPortcullis]

Hi Amine

Thank you for your effort but I cannot see how this will work. The P&L headings aren't measures, they are dimensions in a seperate table. The 78 measures are individually defined, and the calculated rows on the P&L, use Countrows/Switch functionality according to whether the P&L row is a calculated item.

[aj1973]

Each model is different, Can you share your file? Upload it to Onedrive, Dropbox.... and share the link