Forum Discussion
Custom measures list
Hello guys,
I'm new to PowerBi, I'm learning while optimising our team's work.
I have a hard time trying to figure out how to re-create similar order, having the headers one after another. I will be grateful for any tips, e.g. for keywords to search, as for now I end up with results that don't help at all 😅.
For now all I get is something like a complex pivot table, but I really need those fields showing in the way presented below, in a fixed position on the dashboard.
Source:
We have a massive report in Power BI that among other things includes columns for:
1. Environment - main division, everything underneath will have it as a filter
2. If the record is an exception or not (True/False statement, we mostly remove them and only once say how many we excluded)
3. Responsibility - us or the customer
4. Patching method
5. Age - important here is information whether it is <7 or >30 days old
Dashboard:
We used to produce 2 summaries like this (1 for distinct count, 1 for all records):
<<random numbers in the table>>
| distinct count of devices | all records | |
| Environment: Blue Unicorns total | 45252 | 599566 |
| Is Exception (true only) | 8415 | 84696 |
| Number of new records (less than 7 days old) (exceptions filtered out) | 845 | 7486 |
| Progress (how many less or more are there - comparing with last week's data) (exceptions filtered out) | 4566 | 65845 |
| Not covered by monthly patching (exceptions filtered out) | 4866 | 8656 |
| Not covered by monthly patching: Detected more than 30 days ago- ALL (exceptions filtered out) | 1615 | 707 |
| Not covered by monthly patching: Detected more than 30 days ago- US (exceptions filtered out) | 1389 | 653 |
| Not covered by monthly patching: Detected more than 30 days ago- CUST (exceptions filtered out) | 226 | 653 |
| Not covered by monthly patching: Detected less than 30 days ago- ALL (exceptions filtered out) | 124 | 28 |
| Not covered by monthly patching: Detected less than 30 days ago- US | 48 | 162 |
| Not covered by monthly patching: Detected less than 30 days ago- CUST | 76 | 158 |
| Covered by monthly patching/Autoupdated | 3916 | 19 |
| Covered by monthly patching/Autoupdated: Detected more than 30 days ago | 821 | 1667 |
| Covered by monthly patching/Autoupdated: Detected less than 30 days ago | 3095 | 222 |
| Environment: Yellow Bees total | ||
| Number of new records (less than 7 days old) | ||
| Progress (how many less or more are there - comparing with last week's data) | ||
| Not covered by monthly patching | ||
| Not covered by monthly patching: Detected more than 30 days ago- ALL | ||
| Not covered by monthly patching: Detected more than 30 days ago- US | ||
| Not covered by monthly patching: Detected more than 30 days ago- CUST | ||
| Not covered by monthly patching: Detected less than 30 days ago- ALL | ||
| Not covered by monthly patching: Detected less than 30 days ago- US | ||
| Not covered by monthly patching: Detected less than 30 days ago- CUST | ||
| Covered by monthly patching/Autoupdated | ||
| Covered by monthly patching/Autoupdated: Detected more than 30 days ago | ||
| Covered by monthly patching/Autoupdated: Detected less than 30 days ago |
This table also includes data from previous weeks, so ideally it should be adding a new column without replacing previous ones, but let's start somewhere. Later on I will try to make trends from it.
We used to perform this task with a set of fixed pivot tables, but they broke again (they do it each week now - error about issues with data model), and I don't want to waste more time on repairing them while we are moving everything to Power Bi piece by piece.
4 Replies
Hi OptimisationFan ,
In Power Query, it is possible to convert columns into attribute-value pairs, where the columns will become rows.There are three ways to cancel columns in a table:- Unpivot columns- Unpivot other columns- Unpivot selected columns onlyYou can transform the data according to your needs. You can then use the matrix or table visualisations in Power BI to arrange the data. You can use ‘matrix’ for a more flexible row/column structure or ‘table’ for a straightforward interpretation. Add the necessary fields to the corresponding visualisation area.More detailed information can be found in the documentation:If this doesn't solve your problem, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much, Clara.
I will definitely try it out and update this post.
Hi OptimisationFan ,
Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, not yet, as something else more urgent appeared. I was discussing possible solutions with someone here, at my company.
(they suggested creating some additional columns that switch the names of the content & creating levels, I still hesitate if I want to go this way, I need to find out if I can get to the desired result without editing the source table, as this is only one of many charts I am about to create)
Anyway, I hope to have it sorted soon and I will share the answer. Soon, meaning next few days.
