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):   Total Active Inactive Performance V...
Need Help
Tips and Tricks
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! 

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

      Hi ketay ,

       

      May I ask if the provided solution helped in resolving the issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

       

      Thank you!!

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

        Hi ketay ,

         

        I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

         

        Thank you!!