HIde Row in Visual

[edavila]

Good morning and awesome friday,

thanks for trying to help my questions as always

Im trying to figure a way to make a Profit and loss staement and unfurtunaly I dont know how to sort the table very well so I create another row to sort this P&L and now is on the Visual and can be remove, they are probably a DAX formula for doing this but not sure how to code it

link to the table

https://www.dropbox.com/s/2kl1gpq2wfab6zi/Financial%20Dashboard-032417.pbix?dl=0

Thanks like always

 

[Sean · Accepted solution]

@edavilaYou have 3 Locations that shoud be changed from 21 to 24 and then you should be able to use the Sort By Column

Look at the table below each Location should have only 1 Type of Account

However 21 has 2 - it looks like those 3 should be 24  instead of 21

Hope this helps!

[dedelman_clng]

On the "Modeling" tab, try using "Sort by column" to sort "Type of Account" by the Loc-Pos column.  If you get error messages you might have to look at altering your data model a bit in order to get the relationship between Type and Pos to be correct for the re-sort operation.

Hope this helps

David

[edavila]

I try but get an error and I dont have any duplicate number on loc position as I list 1 by 1 by 1 until reaching 414

[dedelman_clng]

Make sure you highlight "Type of Account" before clicking on "Sort By Column".  This looks like you are trying to sort the subtotal column?

[edavila]

still the same issue

[dedelman_clng]

Your data model may not support it.  If "Type of Account" has multiple rows with the same value, then you can get that error.  You may want to look at putting an index column on the table in question (look in Edit Queries), then sort the index on the position.  This may get you what you want.

If you can share the pbix with anonymized data, then I or other may be able to help further.

Hope this helps,

David

P.S. In general a properly sorted grid type report like a balance sheet is better off done in a reporting tool like SSRS rather than a BI tool like PowerBI (because of some of the limitations you are finding like not being able to use columns while hidden).  I don't know your corporate situation, but just food for thought.

[Sean · Accepted solution]

@edavilaYou have 3 Locations that shoud be changed from 21 to 24 and then you should be able to use the Sort By Column

Look at the table below each Location should have only 1 Type of Account

However 21 has 2 - it looks like those 3 should be 24  instead of 21

Hope this helps!

[edavila]

Ok I will make those chage and I understand what you were trying to said as those 3 accounts and rolling the SGA and this year I was going to meve them under Discounts

Thansk let me check your andswer

[Sean · Accepted solution]

@edavila

Here's how you can change them in the Query Editor...

Hope this helps!