Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Howdy,
I need to create a visual table in a specific format, that is essentially a timeline of each month throughout a financial year. I can get the data in many formats already, and already have a dataset with all the RAW data i need... However, going down the list I need different data categories rather than different entities or types - and I've no idea how to do this.
For example, I can get this quite easily:
Month | 1 | 2 | 3 | 4 | 5 | 6 |
Head Office | 8 | 8 | 7 | 7 | 7 | 7 |
Site 1 | 2 | 2 | 2 | 2 | 3 | 3 |
Site 2 | 2 | 2 | 2 | 2 | 2 | 3 |
Site 3 | 3 | 3 | 3 | 3 | 3 | 3 |
But what I actually need is this...
Month | 1 | 2 | 3 | 4 | 5 | 6 |
Staff | 15 | 15 | 14 | 14 | 15 | 16 |
FTE | 10.5 | 10.5 | 10 | 10 | 11 | 12 |
Leavers | 0 | 0 | 1 | 0 | 0 | 0 |
Starters | 0 | 0 | 0 | 0 | 1 | 1 |
And I can't work out how to have different measures per row at all...
I'm attempting to use the Table and Matrix visuals to do this, but have had no joy at all. The only thing I can think of is to create a new table in DAX, have each header at the start and then have each column formula with a huge nested IF to give a different formula/calculation depending on the header - which I'm hoping isn't the right way to go about this!
Any help, as always, is greatly appreciated. Thanks in advance.
Please provide sample data in usable format (not as a picture) and show the expected outcome if it is different from above.
Where did that FTE row come from?
So you need to know how to write the measures for the Leavers and the Starters? Let's say Head Office goes from 8 to 7 because 3 people left and 2 people started. Your data is insufficient to catch that, it would assume a single leaver.
Sorry for the misunderstanding, the first table isn't the data I have - it's the sort of layout I'm able to create using the Power BI 'Table' visual. But what I actually want to be able to create is the 2nd table, but I'm unable to work out how to have a different measure per row rather than per column (or if it's even possible).
My data is currently structured as 1 person per line with various employment details alongside another table with leavers information. That being said, I'm specifically looking for a way in which I can get a measure per row in a table visual rather than on my data itself.
User | Count |
---|---|
122 | |
109 | |
96 | |
59 | |
57 |
User | Count |
---|---|
137 | |
114 | |
102 | |
70 | |
56 |