How can I Calculate measure based on days and causes?

[Yajita24MM]

Hi everybody,

Please I need your help! I have a table that relates hours, months and causes. I am looking for a dax measure or code;

Month	Days	Causes
January	1	Cause 1
January	1	Cause 1
January	12	Cause 1
April	15	Cause 1
April	15	Cause 2
June	20	Cause 2
June	4	Cause 1
July	5	Cause 3
November	10	Cause 3
November	15	Cause 1

The measure i need is KPI = (( Sum of days per cause) / (Sum of total hours per month))*24

For example:  KPI (Cause 1: January) = (48/14)*24 = 82.28

Thank you so much.

Best Regards

Yaja

Perú

[v-xuding-msft · Accepted solution]

Hi @Yajita24MM ,

I created a sample that you could reference to have a try.

Measure =
VAR a =
    CALCULATE ( SUM ( 'Table'[Days] ), ALLEXCEPT ( 'Table', 'Table'[Causes] ) )
VAR b =
    CALCULATE ( SUM ( 'Table'[Days] ), ALLEXCEPT ( 'Table', 'Table'[Month] ) )
RETURN
    DIVIDE ( a, b ) * 24

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Yajita24MM]

Thanks for your help, you save my life😊

[amitchandak]

Try Like

divide(
 CALCULATE (
        max ( 'Table'[Days] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Causes] )
        )
    ),CALCULATE (
        sum ( 'Table'[Days] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Month] )
        )
    ))
	

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin