Pivoting Calculated Columns or Alternative Solution

[Shawkins566]

Hi all, 

I'm looking for some PowerBI help and the best way to go about visualising some data.

I have historic data, it shows cases and has the ID, the date is the week of the data pull, there are 10 KPIs and an overall KPI.  The values for the KPIs and Overall are Red, Amber and Green.  I am trying to determine, how long on average it takes for something that goes to Amber or Red, to return back to Green for each ID.  Not all IDs are added at the same time so there isn't an even amount of rows for ID, some fall off because they are closed, and some may go Amber or Red then back to green multiple times in a cycle. 

For this I did 33 calculated columns 3 per KPI that go like this,

1. 

CALCULATE(  

    MIN('Rolling Data'[Date]),  

    FILTER(  

        'Rolling Data',  

        'Rolling Data'[ID] = EARLIER('Rolling Data'[ID]) &&  

        ('Rolling Data'[KPI 1] = "Red" || 'ERR Rolling Data'[KPI 1] = "Amber")  

    )  

)

2.

VAR FirstROA =  

    CALCULATE(  

        MIN('Rolling Data'[Date]),  

        FILTER(  

            'Rolling Data',  

            'Rolling Data'[ID] = EARLIER('Rolling Data'[ID]) &&  

            ('Rolling Data'[KPI 1] = "Red" || 'Rolling Data'[KPI 1] = "Amber")  

        )  

    )  

RETURN  

CALCULATE(  

    MIN('Rolling Data'[Date]),  

    FILTER(  

        'Rolling Data',  

        'Rolling Data'[ID] = EARLIER('Rolling Data'[ID]) &&  

        'Rolling Data'[Date] > FirstROA &&  

        'Rolling Data'[KPI 1] = "Green"  

    )  

)

3. 

VAR FirstROADate = 'Rolling Data'[KPI 1 RedAmber]  

VAR NextGreenDate = 'Rolling Data'[KPI 1 NextGreenDate]

RETURN  

IF(  

    NOT(ISBLANK(FirstROADate)) && NOT(ISBLANK(NextGreenDate)),  

    DIVIDE(  

        DATEDIFF(FirstROADate, NextGreenDate, DAY),  

        7,  

        0  

    ),  

    BLANK()  

)

So that seems to give me something at least, but when I go to visualise it, the only way I can really do so is in a Matrix Table because I can't unpivot the data as it's all calculated columns rather than something done in transform data.  Ideally I would like a graph, 11 bars for each KPI and the average length of time to return to green. 

Has anyone got any ideas as to how to do this a better way?

I tried to upload dummy data but the system won't seem to let me.

[v-tsaipranay]

Hi @Shawkins566 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @Shawkins566 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @Shawkins566 ,

Thank you for reaching out to the Microsoft fabric community forum and sorry for the dealy response. Also thank you @bhanu_gautam  for your input.

To clarify, the DAX expression provided earlier by bhanu defines a calculated table, not a measure. This table is designed to track, for each ID and KPI, the first instance of a Red or Amber status and the next occurrence of Green, along with the duration between them.

To effectively visualize the average time, it takes for each KPI to return to Green, we recommend first unpivoting your KPI columns in Power Query, so you have a standardized structure with columns like ID, Date, KPI Name, and KPI Status. Then, use the calculated table to capture transitions, and define a DAX measure such as:

Average Duration (Days) = AVERAGEX(KPITransitions, [Duration (Days)])

This will allow you to build a bar chart with KPI names on the X-axis and average duration on the Y-axis, which directly supports your reporting goal.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

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

Thankyou.

[v-tsaipranay]

Hi @Shawkins566 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[bhanu_gautam]

@Shawkins566 Instead of using calculated columns, you can perform the necessary transformations in Power Query. This will allow you to unpivot the data and make it easier to visualize.

Unpivot the KPI columns so that you have a single column for KPI names and another for their values. This will make it easier to calculate the durations and visualize the data.

Calculate the durations for each KPI to return to green using DAX measures instead of calculated columns. This will allow you to create more flexible and dynamic visualizations.

Create a new table to store the durations for each KPI and ID.

DAX
Durations =
ADDCOLUMNS(
SUMMARIZE(
'Rolling Data',
'Rolling Data'[ID],
'Rolling Data'[Attribute]
),
"FirstROA", CALCULATE(
MIN('Rolling Data'[Date]),
FILTER(
'Rolling Data',
'Rolling Data'[ID] = EARLIER('Rolling Data'[ID]) &&
('Rolling Data'[Value] = "Red" || 'Rolling Data'[Value] = "Amber")
)
),
"NextGreenDate", VAR FirstROA = CALCULATE(
MIN('Rolling Data'[Date]),
FILTER(
'Rolling Data',
'Rolling Data'[ID] = EARLIER('Rolling Data'[ID]) &&
('Rolling Data'[Value] = "Red" || 'Rolling Data'[Value] = "Amber")
)
)
RETURN CALCULATE(
MIN('Rolling Data'[Date]),
FILTER(
'Rolling Data',
'Rolling Data'[ID] = EARLIER('Rolling Data'[ID]) &&
'Rolling Data'[Date] > FirstROA &&
'Rolling Data'[Value] = "Green"
)
),
"Duration", VAR FirstROADate = [FirstROA]
VAR NextGreenDate = [NextGreenDate]
RETURN IF(
NOT(ISBLANK(FirstROADate)) && NOT(ISBLANK(NextGreenDate)),
DIVIDE(
DATEDIFF(FirstROADate, NextGreenDate, DAY),
7,
0
),
BLANK()
)
)

[Shawkins566]

Thank you, can you give me a bit more detail? I'm not used to working with measures.  When you say calculate the durations for each KPI to return to green using DAX measures, do you know how I go about doing that with a measure rather than a column? The DAX formula you gave me, is that to create a table or was that the measure?