Pivoting Calculated Columns or Alternative Solution

Hi Anonymous ,

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.