Re: grouped rows with row count - is this possibl...

nielsentm · ‎10-21-2019

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?

Icey · ‎11-06-2019

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

nielsentm · ‎11-06-2019

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!

Icey · ‎11-06-2019

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

Icey · ‎10-21-2019

Hi @nielsentm ,

Is this what you want?

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.

nielsentm · ‎10-22-2019

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'.

Icey · ‎10-22-2019

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.

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

Division Measure =
IF (
    HASONEFILTER ( 'Table'[Project Name] ),
    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.

nielsentm · ‎10-23-2019

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.