Different formulas depending on time granularity used to display my graph

[Anonymous]

Hi all,

I have the following table

Date	Time	UsageInSeconds	User
24/1/2023	10 AM	1800	A
24/1/2023	10 AM	900	B
24/1/2023	11 AM	450	A
24/1/2023	12 PM	900	A
24/1/2023	12 PM	1800	B
24/1/2023	12 PM	450	C
24/1/2023	1 PM	3600	D

And I want to compute the total hourly, daily, monthly, yearly percentage usage, which I would do with these fomulas:

HourlyUsagePercentage = UsageInSeconds/3600 * 100

DailyUsagePercentage = HourlyUsagePercentage / 24

MonthlyUsagePercentage = DailyUsagePercentage / 31 (would be better to adjust depending on the number of days in that month)

YearlyUsagePercentage = DailyUsagePercentage / 365 (account for leap year)

What I need, is a column that display one of the above values depending on the granularity that the graph is currently using to display the data. But how do I get the information regarding the granularity in use?

Also, I'm new to Power BI. That is how I would tackle this prolem, but any different solution/suggestion is welcome.

Thank you!

[SamInogic]

Hi @Anonymous ,

You can use below 5 DAX expressions to create these 5 usage columns:

HourlyUsagePercentage = Usage[UsageInSeconds]/3600

DailyUsagePercentage = (Usage[HourlyUsagePercentage] / 24 )

MonthlyUsagePercentage =

 VAR t = Usage[Date].[Date]

var NoOfDaysInMonth =  

    COUNTROWS(

        CALENDAR(

            FORMAT(t,"YYYY-MM-01") ,

            EDATE(FORMAT(t,"YYYY-MM-01"),1)-1

           )

    )

    return Usage[DailyUsagePercentage] /NoOfDaysInMonth

YearlyUsagePercentage =

var NoOfDaysInYear =

COUNTROWS (

    CALENDAR (

        DATE ( Usage[Date].[Year], 1, 1 ),

        DATE (  Usage[Date].[Year], 12, 31 )

    )

)

return Usage[DailyUsagePercentage] / NoOfDaysInYear

Also, make sure the format for the columns should be Percentage.

Please refer to the below screenshot for the same,

If this answer helps, please mark it as an Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[Anonymous]

Hi @SamInogic and thank you for your reply.

Your solution let me create 5 columns, but how do I visualize all 5 in the same graph, while allowing the drilling feature to select which one to show?