Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
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!
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
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.
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'.
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.
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.
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.
Hi @nielsentm ,
I find this which meets your requirements:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |