Forum Discussion
Assistance Required - Power BI Visual Matrix
Hi Tejas
Ok, great to hear that there is some progress. I took another look to see if I could get rid of the empty columns . I could only find a solution in Power Query and this was the result :
In general the steps taken are as follows : Identifies the Maxmonth and two months before ( Query : MaxMonthQuery ) , which joins with the Grouped average per assignment group ( Query : GroupedAverageQuery ) . This then finally joins with the MaindataQuery ( filtered to show only 3 months prior to max date of the data ) to merge the average SLA% per group.
For the updated file see the same folder I placed the other Power BI files in.
Hope this helps
Antonio
Hi Lakhani04
I suggest the following :
SLA % 3M =
VAR MaxDate =
CALCULATE(
MAX(Data[Date]),
REMOVEFILTERS(Data)
)
VAR Last3Months =
DATESINPERIOD(
Data[Date],
MaxDate,
-3,
MONTH
)
RETURN
CALCULATE(
AVERAGE(Data[SLA %]),
FILTER(
ALL(Data),
Data[Date] IN Last3Months &&
Data[Assignment Group] = MAX(Data[Assignment Group])
)
)
Result tested with slicer ( table with percentages not affected by slicer - for previous 3 months > as per max date of the data ) :
Below is some further information so that you can adjust the measure if required :
-MaxDate gets the latest date from the entire table, ignoring any filters (like slicers).
-Last3Months creates a list of dates from the last 3 months up to that MaxDate.
-RETURN calculates the average SLA % only for rows in those 3 months and only for the currently selected assignment group.
Hope this helps
Antonio
Tried removefilters no luck.