Preparing a operational dashboard

[ketay]

I have set of data which comprises of data of devices/cameras that are date, category, asset name, active, inactive, total, percentage. 
The data is based on 'reported date' which is weekly on the Fridays.

1) How do I show the total of devices without totalling all the number of the devices?

2) How do I shows interaction between month? then week? 

3) How to join with Commentary data which only have monthly comments?

Any advice or assistance would be much appreciate. 

Kind regards,

Koh T.

[rajendraongole1 · Accepted solution]

Hi @ketay - If you want to display the total number of unique devices without summing up all the weekly values,

you can use DISTINCTCOUNT function to get the same. 

DISTINCTCOUNT function (DAX) - DAX | Microsoft Learn

2.Ensure you have a date table marked as a "Date Table" in Power BI. This table should include columns for Year, Month, Week, and any other needed date hierarchies.Create Measures for Month and Week Analysis

3. If your commentary data is only on a monthly level, you can join it using a relationship on the month field

using calculated column:

Month = FORMAT('Date'[Date], "YYYY-MM")

Use a LOOKUPVALUE or a relationship-based approach to bring the commentary into your visuals.

Hope this works. please check.

Set and use date tables in Power BI Desktop - Power BI | Microsoft Learn

[grazitti_sapna · Accepted solution]

Hi @ketay ,

Total Devices Without Double Counting:
Rather than adding a "total" column that may double-count the same device because data is logged weekly, make a measure out of a distinct count on your device identifier (e.g., Asset Name or Device ID). For instance:
Total Devices = DISTINCTCOUNT('Data'[Asset Name])

This measure will only count each device once, no matter how many times it shows up in the data.

Interaction Between Month and Week
In order to facilitate smooth interactivity between week and month views, develop an all-encompassing Date table with year, month, week, etc. fields. Have a relationship with your fact table based on the 'reported date' field from this Date table. And, in your charts, employ the hierarchy of Date table (i.e., Year > Month > Week) or individual slicers for Month and Week. This will enable users to drill down from a monthly overview into weekly information or filter the data at varying levels of granularity.

Joining with Monthly Commentary Data:
As your commentary data exists at a monthly level, employ your Date table as an interface between the commentary table and fact table. Ensure that the Date table contains a month column to match the commentary data. Create a relationship from the commentary table to the Date table on the month field next. This allows commentary to filter or display alongside your weekly data but remains tied to the correct month.

Following these steps should assist you in properly counting devices, generating interactive month-to-week views, and joining commentary data with your primary dataset smoothly.
 
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[v-pnaroju-msft]

Hi ketay,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi ketay,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi ketay,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @rajendraongole1,@grazitti_sapna, for your response.

Hi ketay,

We appreciate your inquiry posted on the Microsoft Fabric Community Forum.

Based on my understanding, please follow the steps outlined below, which may help resolve the issue:

1. Use the DAX formula provided to create a comprehensive Date Table with Year, Month, and Week columns. Ensure that this table is marked as a Date Table.
   
   DateTable =
   ADDCOLUMNS(
   CALENDAR(DATE(2024,1,1), DATE(2025,12,31)),
   "Year", YEAR([Date]),
   "Month", FORMAT([Date], "MMM YYYY"),
   "MonthNumber", FORMAT([Date], "YYYYMM"),
   "Week", WEEKNUM([Date],2),
   "Week Ending", [Date] + (7 - WEEKDAY([Date],2))
   )

2. Establish relationships by connecting the Reported Date column from your fact table to the Date column in the Date Table.

3. Instead of using DISTINCTCOUNT, please create the measures indicated to correctly aggregate your values.
   Total Devices = SUM('YourDataTable'[Total])
   Active Devices = SUM('YourDataTable'[Active])
   Inactive Devices = SUM('YourDataTable'[Inactive])
   Performance (%) = DIVIDE([Active Devices], [Total Devices], 0)
   Total Devices (Monthly) =
   CALCULATE([Total Devices], REMOVEFILTERS('DateTable'[Week]))

4. Use the Date Hierarchy (Year > Month > Week) in your visuals and enable Drill Mode to smoothly navigate from months to weeks.

5. Add a Month column in your Commentary Table and relate it to the Month column in the Date Table to align comments with your monthly data.
   CommentaryMonth = FORMAT('CommentaryTable'[CommentDate], "MMM YYYY")
   
   

If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[grazitti_sapna · Accepted solution]

Hi @ketay ,

Total Devices Without Double Counting:
Rather than adding a "total" column that may double-count the same device because data is logged weekly, make a measure out of a distinct count on your device identifier (e.g., Asset Name or Device ID). For instance:
Total Devices = DISTINCTCOUNT('Data'[Asset Name])

This measure will only count each device once, no matter how many times it shows up in the data.

Interaction Between Month and Week
In order to facilitate smooth interactivity between week and month views, develop an all-encompassing Date table with year, month, week, etc. fields. Have a relationship with your fact table based on the 'reported date' field from this Date table. And, in your charts, employ the hierarchy of Date table (i.e., Year > Month > Week) or individual slicers for Month and Week. This will enable users to drill down from a monthly overview into weekly information or filter the data at varying levels of granularity.

Joining with Monthly Commentary Data:
As your commentary data exists at a monthly level, employ your Date table as an interface between the commentary table and fact table. Ensure that the Date table contains a month column to match the commentary data. Create a relationship from the commentary table to the Date table on the month field next. This allows commentary to filter or display alongside your weekly data but remains tied to the correct month.

Following these steps should assist you in properly counting devices, generating interactive month-to-week views, and joining commentary data with your primary dataset smoothly.
 
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[ketay]

HI grazitti_sapna,

Thank you so much for the reply. Really helps. Just wondering, if I have the following data, reported of the counts on a weekly basis (Fridays of each week):

Total	Active	Inactive	Performance	Value / Percentage	Date	Asset Name	Category Type
60	52	9	87%	52 / 60  (87%)	6/12/2024	Device Light A	Lighting
14	10	3	71%	10 / 14  (71%)	6/12/2024	Device Light B	Lighting
24	18	4	75%	18 / 24  (75%)	6/12/2024	Device Light C	Lighting
20	4	16	20%	4 / 20  (20%)	6/12/2024	Device Light E	Lighting
46	36	10	78%	36 / 46  (78%)	6/12/2024	Device Light F	Lighting
11	7	0	64%	7 / 11  (64%)	6/12/2024	Device Light G	Lighting
175	127	42	73%	127 / 175  (73%)	6/12/2024	Total to date	Lighting
60	52	8	87%	52 / 60  (87%)	13/12/2024	Device Light A	Lighting
14	11	3	79%	11 / 14  (79%)	13/12/2024	Device Light B	Lighting
24	18	6	75%	18 / 24  (75%)	13/12/2024	Device Light C	Lighting
20	4	16	20%	4 / 20  (20%)	13/12/2024	Device Light E	Lighting
46	36	10	78%	36 / 46  (78%)	13/12/2024	Device Light F	Lighting
11	7	4	64%	7 / 11  (64%)	13/12/2024	Device Light G	Lighting
175	128	47	73%	128 / 175  (73%)	13/12/2024	Light Total to date	Lighting
60	52	7	87%	52 / 60  (87%)	20/12/2024	Device Light A	Lighting
14	11	3	79%	11 / 14  (79%)	20/12/2024	Device Light B	Lighting
24	18	6	75%	18 / 24  (75%)	20/12/2024	Device Light C	Lighting
20	4	16	20%	4 / 20  (20%)	20/12/2024	Device Light E	Lighting
46	36	10	78%	36 / 46  (78%)	20/12/2024	Device Light F	Lighting
11	8	1	73%	8 / 11  (73%)	20/12/2024	Device Light G	Lighting
175	129	43	74%	129 / 175  (74%)	20/12/2024	Light Total to date	Lighting
60	52	7	87%	52 / 60  (87%)	27/12/2024	Device Light A	Lighting
14	11	3	79%	11 / 14  (79%)	27/12/2024	Device Light B	Lighting
24	18	6	75%	18 / 24  (75%)	27/12/2024	Device Light C	Lighting
20	4	16	20%	4 / 20  (20%)	27/12/2024	Device Light E	Lighting
46	36	10	78%	36 / 46  (78%)	27/12/2024	Device Light F	Lighting
11	8	1	73%	8 / 11  (73%)	20/12/2024	Device Light G	Lighting
175	129	43	74%	129 / 175  (74%)	20/12/2024	Light Total to date	Lighting
12	12	0	100%	12 / 12 (100%)	6/12/2024	Device Door A	Door
9	6	3	67%	6 / 9 (67%)	6/12/2024	Device Door B	Door
14	14	0	100%	14 / 14 (100%)	6/12/2024	Device Door C	Door
6	6	6	100%	6 / 6 (100%)	6/12/2024	Device Door E	Door
4	1	3	25%	1 / 4 (25%)	6/12/2024	Device Door F	Door
45	39	12	78%	39 / 45 (78%)	6/12/2024	Total to date	Door
12	12	0	100%	12 / 12 (100%)	13/12/2024	Device Door A	Door
9	6	3	67%	6 / 9 (67%)	13/12/2024	Device Door B	Door
14	12	2	86%	12 / 14 (86%)	13/12/2024	Device Door C	Door
6	6	6	100%	6 / 6 (100%)	13/12/2024	Device Door E	Door
4	1	3	25%	1 / 4 (25%)	13/12/2024	Device Door F	Door
45	37	14	75%	37 / 45 (75%)	13/12/2024	Total to date	Door
12	12	0	100%	12 / 12 (100%)	20/12/2024	Device Door A	Door
9	6	3	67%	6 / 9 (67%)	20/12/2024	Device Door B	Door
14	14	0	100%	14 / 14 (100%)	20/12/2024	Device Door C	Door
6	0	6	0%	0 / 6 (0%)	20/12/2024	Device Door E	Door
4	1	3	25%	1 / 4 (25%)	20/12/2024	Device Door F	Door
45	33	12	58%	33 / 45 (58%)	20/12/2024	Total to date	Door
12	12	0	100%	12 / 12 (100%)	27/12/2024	Device Door A	Door
9	7	2	78%	7 / 9 (78%)	27/12/2024	Device Door B	Door
14	14	0	100%	14 / 14 (100%)	27/12/2024	Device Door C	Door
6	0	6	0%	0 / 6 (0%)	27/12/2024	Device Door E	Door
4	3	1	75%	3 / 4 (75%)	27/12/2024	Device Door F	Door
45	36	9	71%	36 / 45 (71%)	27/12/2024	Total to date	Door

What would be the best methods to shows the counts from month to month, then week to week? Using distinct count seems to only counts the rows of distinct asset, but could not count the value on each. Could you please shine some light of resolving this? Many thanks.  

[rajendraongole1 · Accepted solution]

Hi @ketay - If you want to display the total number of unique devices without summing up all the weekly values,

you can use DISTINCTCOUNT function to get the same. 

DISTINCTCOUNT function (DAX) - DAX | Microsoft Learn

2.Ensure you have a date table marked as a "Date Table" in Power BI. This table should include columns for Year, Month, Week, and any other needed date hierarchies.Create Measures for Month and Week Analysis

3. If your commentary data is only on a monthly level, you can join it using a relationship on the month field

using calculated column:

Month = FORMAT('Date'[Date], "YYYY-MM")

Use a LOOKUPVALUE or a relationship-based approach to bring the commentary into your visuals.

Hope this works. please check.

Set and use date tables in Power BI Desktop - Power BI | Microsoft Learn