cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

grouped rows with row count - is this possible with matrix

Is the following layout possible with power bi? I need the layout to appear as in the first screenshot below.

 

The closest I have been able to get in power bi is the matrix in the second screenshot. As you can see the Total row colors are not consistent for whatever reason (some are grey, some are white), and I don't want the Total to be a separate column. I had to hard code a new Total column with values of 1 for every row to get it to even have a total. This is just a simple row count.

 

Am I wasting my time here?

power bi example.pngUntitled.png

 

 

 

 

8 REPLIES 8
Highlighted
Community Support
Community Support

Re: grouped rows with row count - is this possible with matrix

Hi @nielsentm ,

Is this what you want?

matrix.PNGmatrix2.PNG

If so, you can create your measure like so:

Division Measure =
IF (
    HASONEVALUE ( 'Table'[Fiscal Year] ),
    MAX ( 'Table'[Division] ),
    COUNT ( 'Table'[Division] )
)

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: grouped rows with row count - is this possible with matrix

hi,

Thanks for your post, I can't get your suggestion to work as needed.

 

Here is a sample dataset. The issue I am hung up on with your example is I can have multiple projects for each fiscal year (see in screenshot below (esc id 100, fiscal year 2017). This matrix only seems to take 'First Value'.

 

Untitled3.png

Highlighted
Community Support
Community Support

Re: grouped rows with row count - is this possible with matrix

Hi @nielsentm ,

The "First" aggregation doesn't affect the sample values for each specific row, only the subtotal and total values. This is because of the Row Context and Filter Context

And something you should pay attention to is that Power BI removes duplicate rows from a table/matrix by default. This is due to Power BI being a aggragation tool. The way around this would be to create a index column to provide the uniqueness of rows.

first.PNG

 

And the screenshot below is better. The "Division Measure" is:

Division Measure =
IF (
    HASONEFILTER ( 'Table'[Project Name] ),
    MAX ( 'Table'[Division] ),
    COUNT ( 'Table'[Division] )
)

group4.PNG

 

Best Regards,
Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: grouped rows with row count - is this possible with matrix

sgrid.png

 

hi,

Thanks again for your information, very helpful.

I can see that matrix is probably not the best tool for what I need, though it seems to be the only option for a grouped style layout. I have about 10 other fields in addition to the simple dataset I posted earlier, see the screenshot above. The aggregated style of the matrix is kind of overwhelming visually once you add all these fields. I've gotten it 'sort of' working by removing the tree style layout and the subtotals that display on each field. Doing that removed the grand total option and I couldn't figure out how to get it back, so I had to fake that with 'Cards' at the bottom to display Grand Totals.

 

I was not able to figure out how to use a Measure like you provided to show the Total in the last column (Final Decision Date). My workaround was to add a Total column and change the background/text color so you can't see the individual Total numbers in each row, only the sub Total. I tried this measure - but could not figure out how to just display the Date 'StageGate'[CompleteStageGate.FinalDecisionDate]. I am guessing this is also due to aggregation and will probably not be possible.

 

Measure =
IF (
    HASONEVALUE ( 'StageGate'[FiscalYear] ),
    'StageGate'[CompleteStageGate.FinalDecisionDate], //<-- this does not compile, just want to display the value of this field
    COUNT ( 'StageGate'[Id] )
)
 

 

Thanks for the assistance, I did learn a lot from your posts.  It just seems like this type of layout is a simple and common request and I feel like everything I did to get this point was a hack. I wish that power bi could accomodate this easier.

 

 

 

 

Highlighted
Community Support
Community Support

Re: grouped rows with row count - is this possible with matrix

Hi @nielsentm ,

I find this which meets your requirements:

project.PNG

project2.PNG

PBIX file attached.

 

 

Best Regards,
Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Highlighted
Community Support
Community Support

Re: grouped rows with row count - is this possible with matrix

Hi @nielsentm ,


Is this problem solved?


If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.


If not, please let me know.


Best Regards
Icey

Highlighted
Frequent Visitor

Re: grouped rows with row count - is this possible with matrix

 

Unfortunately no, I don't think power bi is a good option for what we are trying to do in this case. In other cases it seems to work great though. Your responses were very helpful for learning more about power bi. But this issue and a few others will probably prevent us from using this product for our customers. thanks again!

 

Highlighted
Community Support
Community Support

Re: grouped rows with row count - is this possible with matrix

Hi @nielsentm ,

We solved the problem you originally asked, right? If so, please accept the corresponding reply as an answer. Thank you.

 

Best Regards,

Icey

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors