Power BI Data shaping - dates match figures in certain column

[N7MESTE3]

Hi,

I've been playing with this for a while and I cannot find an efficient solution.

I have a large data set with monthly dates ranging a number of years across a lot of columns, under said dates are figures against certain categories, however I need the dates to show in one column while also matching the figure against the category.

This also needs to be set up in a way that it will automatically update as changes are made as it is quite an active document. 

I have added an image of what it looks like and another to what it should look like (the one it should look like does not update automatically so it isn't of use).

I hope that makes sense, thank you!What I would prefer it to look likeThis is the current data set

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[N7MESTE3]

Thank you for replying, as I am on my work computer I cannot share a link but I have put the data into two tables. This is the raw data and I will reply agin with what I need it to look like. As I said I need it to update automatically as I have it linked to the original excel sheet with the intention of linking mine to PBI. 

Efficiency Type

Efficiency Header

Efficiency Sub-Header

Efficiency

Category Code

Forecast/Delivered

FORECAST - RAG STATUS

RAG SCORE MATRIX (Score 1-25)

01/04/2021

01/05/2021

01/06/2021

01/07/2021

01/08/2021

01/09/2021

FORECAST 6 Monthly Apr - Sep inc 2021/22 Sub-Total

PROJECT

Dummy data

Dummy Data 2

DR

Delivered

GREEN

PROJECT

Dummy data

Dummy Data 2

NP

Forecast

AMBER

Score 11

0

PROJECT

Dummy data

Dummy Data 2

NP

Delivered

GREEN

PROJECT

Dummy data

Dummy Data 2

DR

Forecast

AMBER

0

PROJECT

Dummy data

Dummy Data 2

DR

Forecast

AMBER

Score 07

0

PROJECT

Dummy data

Dummy Data 2

NP

Forecast

AMBER

Score 06

0

PROJECT

Dummy data

Dummy Data 2

DR

Delivered

GREEN

PROJECT

Dummy data

Dummy Data 2

DR

Forecast

AMBER

Score 13

0

PROJECT

Dummy data

Dummy Data 2

DR

Forecast

AMBER

Score 13

0

PROJECT

Dummy data

Dummy Data 2

DR

Delivered

GREEN

26726.77

53453.54

53453.54

53453.54

53453.54

53453.54

BAU Efficiencies

Dummy data

Dummy Data 2

NP

Forecast

Amber

Score 24

0

BAU Efficiencies

Dummy data

Dummy Data 2

DR

Delivered

GREEN

[N7MESTE3]

Monthly Data[Efficiency Type]	Monthly Data[Efficiency Header]	Monthly Data[Efficiency Sub-Header]	Monthly Data[Efficiency]	Monthly Data[Category Code]	Monthly Data[Forecast/Delivered]	Monthly Data[FORECAST - RAG STATUS]	Monthly Data[Monthly Breakdown]	Monthly Data[Efficiency Value]
BAU Efficiencies	Dummy Data		Dummy Data 2	DR	Delivered	GREEN	01/04/2024	169.94
BAU Efficiencies	Dummy Data		Dummy Data 2	DR	Delivered	GREEN	01/05/2024	339.88
BAU Efficiencies	Dummy Data		Dummy Data 2	DR	Delivered	GREEN	01/06/2024	339.88
BAU Efficiencies	Dummy Data		Dummy Data 2	DR	Delivered	GREEN	01/07/2024	339.88
BAU Efficiencies	Dummy Data		Dummy Data 2	DR	Delivered	GREEN	01/08/2024	339.88
BAU Efficiencies	Dummy Data		Dummy Data 2	DR	Delivered	GREEN	01/09/2024	339.88
BAU Efficiencies	Dummy Data		Dummy Data 2	DR	Delivered	GREEN	01/10/2024	339.88
BAU Efficiencies	Dummy Data		Dummy Data 2	DR	Delivered	GREEN	01/11/2024	339.88
BAU Efficiencies	Dummy Data		Dummy Data 2	DR	Delivered	GREEN	01/12/2024	339.88
BAU Efficiencies	Dummy Data		Dummy Data 2	DR	Delivered	GREEN	01/01/2025	339.88
BAU Efficiencies	Dummy Data		Dummy Data 2	DR	Delivered	GREEN	01/02/2025	339.88
BAU Efficiencies	Dummy Data		Dummy Data 2	DR	Delivered	GREEN	01/03/2025	339.88
BAU Efficiencies	Dummy Data		Dummy Data 2	NP	Forecast	AMBER	01/12/2024	134364.99
BAU Efficiencies	Dummy Data		Dummy Data 2	NP	Delivered	GREEN	01/01/2023	1195818.84

[v-yilong-msft]

Hi @N7MESTE3 ,

I create a table as you mentioned.

Then I think you can create a measure.

RowColor = 
SWITCH (
    TRUE (),
    MAX ( 'Table'[FORECAST - RAG STATUS] ) = "AMBER", "Yellow",
    MAX ( 'Table'[FORECAST - RAG STATUS] ) = "GREEN", "Green",
    "White"
)

Currently in Power BI you can only colorize entire columns.

If you want to color all rows, you can only repeat the coloring.

Best Regards

Yilong Zhou

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

[N7MESTE3]

Hi Yilong,

Thank you for getting back to me, unfortunately that isn't the problem I'm experiencing. I can filter to show Green/Amber and colour it, the problem is linking the Efficiency to figures shown across each date column. I don't think the example table I have given really shows what I need, the figures in each date column hold multiple figures in the different efficiency rows, I need the figures to match across each date and efficiency without losing any of the data with it automatically updating. 

Unfortunately as I am unable to upload the full data I might have to figure this out on my own!

[v-yilong-msft]

Hi @N7MESTE3 ,

I very much hope that you will be able to solve the problem you are experiencing through your own efforts.

If possible, after you have solved the problem, you can present your thoughts and results, which can help other users on the forum.

Best Regards

Yilong Zhou

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

[N7MESTE3 · Accepted solution]

Hi,
So I was able to get the answer eventually, it's a bit embarrassing but I am sharing in case anyone forgets about this simple option as I did!

I simply needed to go into Power Query and unpivot the date columns - problem solved. This is something I had done before but completely forgot. Sometimes the simple solutions are right in front of us! I appreciate all the help.