Forum Discussion

ketay's avatar
ketay
Frequent Visitor
1 year ago

Data Prep in Power Bi for Operational Dashboard

Hi Super Users,

 

I having some dillema with this data. If I have the following data, reported of the counts on a weekly basis (Fridays of each week):

 

TotalActiveInactivePerformanceValue / PercentageDateAsset NameCategory Type
6052987%52 / 60
 (87%)		6/12/2024Device Light ALighting
1410371%10 / 14
 (71%)		6/12/2024Device Light BLighting
2418475%18 / 24
 (75%)		6/12/2024Device Light CLighting
2041620%4 / 20
 (20%)		6/12/2024Device Light ELighting
46361078%36 / 46
 (78%)		6/12/2024Device Light FLighting
117064%7 / 11
 (64%)		6/12/2024Device Light GLighting
1751274273%127 / 175
 (73%)		6/12/2024Total to dateLighting
6052887%52 / 60
 (87%)		13/12/2024Device Light ALighting
1411379%11 / 14
 (79%)		13/12/2024Device Light BLighting
2418675%18 / 24
 (75%)		13/12/2024Device Light CLighting
2041620%4 / 20
 (20%)		13/12/2024Device Light ELighting
46361078%36 / 46
 (78%)		13/12/2024Device Light FLighting
117464%7 / 11
 (64%)		13/12/2024Device Light GLighting
1751284773%128 / 175
 (73%)		13/12/2024Light Total to dateLighting
6052787%52 / 60
 (87%)		20/12/2024Device Light ALighting
1411379%11 / 14
 (79%)		20/12/2024Device Light BLighting
2418675%18 / 24
 (75%)		20/12/2024Device Light CLighting
2041620%4 / 20
 (20%)		20/12/2024Device Light ELighting
46361078%36 / 46
 (78%)		20/12/2024Device Light FLighting
118173%8 / 11
 (73%)		20/12/2024Device Light GLighting
1751294374%129 / 175
 (74%)		20/12/2024Light Total to dateLighting
6052787%52 / 60
 (87%)		27/12/2024Device Light ALighting
1411379%11 / 14
 (79%)		27/12/2024Device Light BLighting
2418675%18 / 24
 (75%)		27/12/2024Device Light CLighting
2041620%4 / 20
 (20%)		27/12/2024Device Light ELighting
46361078%36 / 46
 (78%)		27/12/2024Device Light FLighting
118173%8 / 11
 (73%)		20/12/2024Device Light GLighting
1751294374%129 / 175
 (74%)		20/12/2024Light Total to dateLighting
12120100%12 / 12
(100%)		6/12/2024Device Door ADoor
96367%6 / 9
(67%)		6/12/2024Device Door BDoor
14140100%14 / 14
(100%)		6/12/2024Device Door CDoor
666100%6 / 6
(100%)		6/12/2024Device Door EDoor
41325%1 / 4
(25%)		6/12/2024Device Door FDoor
45391278%39 / 45
(78%)		6/12/2024Total to dateDoor
12120100%12 / 12
(100%)		13/12/2024Device Door ADoor
96367%6 / 9
(67%)		13/12/2024Device Door BDoor
1412286%12 / 14
(86%)		13/12/2024Device Door CDoor
666100%6 / 6
(100%)		13/12/2024Device Door EDoor
41325%1 / 4
(25%)		13/12/2024Device Door FDoor
45371475%37 / 45
(75%)		13/12/2024Total to dateDoor
12120100%12 / 12
(100%)		20/12/2024Device Door ADoor
96367%6 / 9
(67%)		20/12/2024Device Door BDoor
14140100%14 / 14
(100%)		20/12/2024Device Door CDoor
6060%0 / 6
(0%)		20/12/2024Device Door EDoor
41325%1 / 4
(25%)		20/12/2024Device Door FDoor
45331258%33 / 45
(58%)		20/12/2024Total to dateDoor
12120100%12 / 12
(100%)		27/12/2024Device Door ADoor
97278%7 / 9
(78%)		27/12/2024Device Door BDoor
14140100%14 / 14
(100%)		27/12/2024Device Door CDoor
6060%0 / 6
(0%)		27/12/2024Device Door EDoor
43175%3 / 4
(75%)		27/12/2024Device Door FDoor
4536971%36 / 45
(71%)		27/12/2024Total to dateDoor

 

The data is based on 'reported date' which is weekly on the Fridays.

1) 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. 

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

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

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

 

Any good link or suggestion of using DateTable. Could you please shine some light of resolving this? Many thanks.  

Need Help
Tips and Tricks

9 Replies

    • ketay's avatar
      ketay
      Frequent Visitor
      1 year ago

      Thanks for the reply parry2k. I will adpot the date table and make use of it. 

      Do you have any recommendation towards displaying the total active and total inactive per category and per item? If I use distinct count seems to only counts the rows of distinct asset, but could not count the value on each. 

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    1 year ago

    ketay sorry not sure if I understood your question correctly, for active and inactive, you create two measures, and put category and item, and these measures in the visual:

     

    Active = SUM ( Table[Active Column] )

InActive = SUM ( Table[InActive Column] )
    • ketay's avatar
      ketay
      Frequent Visitor
      1 year ago

      Thanks for the reply. 
      I'll try my best to explain more. So, each row is recording the weekly counts, for example, it reports on the Category = Lighting and Asset Name = Device A, B, C and etc on 6/12/2024, with:

      Total = Total Asset count

      Active = Total Active count

      Inactive = Total Inactive count

      And get repeats on the next week...and so forth. 

       

      My challenge is when I sum the values, it returns the accumulated total of all the devices. What would be the best way to prep the data so that I can provide the actual counts across months, weeks and even days.

      Appreciate any recommendation or tips to this. Thanks in advance. 

      TotalActiveInactivePerformanceValue / PercentageDateAsset NameCategory Type
      6052987%52 / 60
       (87%)      		6/12/2024Device Light ALighting
      1410371%10 / 14
       (71%)      		6/12/2024Device Light BLighting
      2418475%18 / 24
       (75%)      		6/12/2024Device Light CLighting
      2041620%4 / 20
       (20%)      		6/12/2024Device Light ELighting
      46361078%36 / 46
       (78%)      		6/12/2024Device Light FLighting
      117064%7 / 11
       (64%)      		6/12/2024Device Light GLighting
      1751274273%127 / 175
       (73%)      		6/12/2024Total to date

      Lighting

      • v-sathmakuri's avatar
        v-sathmakuri
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi ketay ,

         

        Thank you for reaching out to Microsoft Fabric Community.

         

        Sorry for the delay in response.

         

        Please use the below measures to get the expected results. Also attached the pbix file for reference.

         

        Create a date table and establis relation ship with your table on date columns. 

        DateTable =
        ADDCOLUMNS (
            CALENDAR (DATE(2024, 1, 1), DATE(2025, 12, 31)),
            "Year", YEAR([Date]),
            "Month", FORMAT([Date], "MMMM"),
            "MonthNum", MONTH([Date]),
            "Week", WEEKNUM([Date]),
            "Week Ending", [Date]  // since you use Friday as reporting
        )
         
        use below measure to create week rage from friday to thursday
         
        WeekRange =
        VAR StartOfWeek =
            VAR d = [Date]
            VAR offset =
                SWITCH(
                    WEEKDAY(d, 2),
                    1, -3,
                    2, -4,
                    3, -5,
                    4, -6,
                    5, 0,
                    6, -1,
                    7, -2
                )
            RETURN d + offset
        VAR EndOfWeek = StartOfWeek + 6
        RETURN
        FORMAT(StartOfWeek, "MMM d") & " – " & FORMAT(EndOfWeek, "MMM d")
         
        Use below measures to calculate Total Asset count, Total Active count and Total Inactive count
         
        Active Devices (Latest Per Asset) =
        SUMX(
            VALUES('Table'[Asset Name]),
            VAR latestDate =
                CALCULATE(
                    MAX('Table'[Date])
                )
            RETURN
                CALCULATE(
                    MAX('Table'[Active]),
                    'Table'[Date] = latestDate
                )
        )
         
        Inactive Devices (Latest Per Asset) =
        SUMX(
            VALUES('Table'[Asset Name]),
            VAR latestDate =
                CALCULATE(
                    MAX('Table'[Date])
                )
            RETURN
                CALCULATE(
                    MAX('Table'[Inactive]),
                    'Table'[Date] = latestDate
                )
        )
         
        Total Devices (Latest Per Asset) =
        SUMX(
            VALUES('Table'[Asset Name]),
            VAR latestDate =
                CALCULATE(
                    MAX('Table'[Date])
                )
            RETURN
                CALCULATE(
                    MAX('Table'[Total]),
                    'Table'[Date] = latestDate
                )
        )

         

        If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    1 year ago

    ketay I'm still not able to wrap my head around your request. Could you please share what will be the outcome based on your example?