Compliance against the Targets

[HSK25]

I have a daily targets of supplying seven skill types of workers to ten sites . the targets for each skill type is different for different sites. I supply the workers sometimes less or more than the daily target. I keep a daywise record of each person, mentioning their name, designation and the site. the records shows only those who are present on a particular date. there is no record of absents. i want to visualise the compliance ( site wise as well as designation wise) on daily, weekly, monthly, quarterly and Fiscal yearly (Apr-Mar) basis. I tried but did not get the correct measure for the target. Please help.

[DataNinja777]

Hi @HSK25 ,

To visualize compliance effectively, you need to calculate it as the ratio of the number of workers supplied to the target for each skill type and site. Start by preparing your data model. Create a fact table containing daily records of workers who were present, with columns like Date, Name, Designation, and Site.

Then, prepare a target table that includes Site, Designation, Target, and, if targets vary over time, a Date column. If targets are static, the Date column can be omitted.

Finally, set up a calendar table to enable slicing by fiscal year, quarters, and other time periods. This table can be generated using a DAX formula such as:

Calendar = 
ADDCOLUMNS(
    CALENDAR(MIN(FactTable[Date]), MAX(FactTable[Date])),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Fiscal Year", IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1)
)

Establish relationships by linking the fact table to the calendar table on the Date column and linking the fact and target tables using Site and Designation.

Next, calculate measures to analyze compliance. Start with Total Supplied, which counts the workers supplied using the formula:

Total Supplied = COUNTROWS('Fact Table')

For Total Target, aggregate the targets dynamically based on the time period using:

Total Target = SUM('Target Table'[Target])

The compliance percentage can then be calculated as:

Compliance % = DIVIDE([Total Supplied], [Total Target], 0)

To track cumulative performance, use functions like DATESYTD, DATESMTD, or DATESQTD. For example, monthly cumulative compliance can be calculated with:

Cumulative Supplied = 
CALCULATE([Total Supplied], DATESMTD('Calendar'[Date]))

Cumulative Target = 
CALCULATE([Total Target], DATESMTD('Calendar'[Date]))

Cumulative Compliance % = DIVIDE([Cumulative Supplied], [Cumulative Target], 0)

To visualize the results, you can use line or column charts to show daily compliance, with Date on the X-axis and Compliance % on the Y-axis. Add slicers for Site and Designation to allow focused analysis. For weekly, monthly, quarterly, or fiscal year compliance, create aggregated measures using the time intelligence functions above and display them in visualizations like line charts or matrix tables. A heatmap or matrix can also effectively display compliance levels by site and designation over time, with conditional formatting to highlight compliance levels.

It’s important to handle missing data carefully, as absent workers are not recorded. Ensure that targets are linked to the full date range from the calendar table to avoid gaps in calculations. Additionally, slicers for site, designation, and time periods (daily, weekly, monthly, etc.) will enhance dynamic analysis. If the target measure does not calculate correctly, check the relationships between tables and verify that the aggregations are using the correct context, possibly with functions like SUMX or CALCULATE. If you have sample data or details of your current model, I can assist further in refining these formulas.

Best regards,

[HSK25]

Thanks for the solution.

 The Target measure is resulting only the daily targets. and, there is an issue in the reltionship between the FACT and TARGET table (cardnality Many - Many).

[Rupak_bi]

Please share sample data and desired output so that we can understand the exact scenario and try to solve.