Matrix calculation Issue - DAX

[WinterGarden]

Hi All,

I have a matrix visual similar to below table, where i have to show "Billable_Utilization"(2nd Column)
Billable_Utilization = (Billable_Hours / (Standard_Hr1 - Leave_Hours))*100

As per the data i am getting values similar to 2nd column(Billable_Utilization - As per my calculation)
But the correct value of Billable Utilization should be like  Column 6( Correct Billable_Utilization )

Requestor	Billable_Utilization - As per my calculation	Billable_Hours	Standard_Hr1	Leave_Hours	Correct Billable_Utilization  (Billable_Hours /(Standard_Hr1 - Leave_Hours))*100
A	22.97%	36.75	160		25.52%     (36.75 / (160-16))*100
B	15.27%	24.43	160		16.96% (24.43 / (160-16))*100
C	14.84%	23.75	160		16.49% (23.75/(160-16))*100
D	10.34%	16.55	160		11.49 (16.55/(160-16))*100
Non-Billable			160	16

Below is the dax:

1) Billable_Utl = DIVIDE([Billable_Hours],[Standard_Hr1]-[Leave_Hours])

2)Billable_Hours = CALCULATE(SUM(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Time_In_Hr]),FILTER(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User,vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Billable]="Billable"))

3) Standard_Hr1 = sum(vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data[stdd_hr] )

4)

Leave_Hours =

CALCULATE(

    SUM(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Time_In_Hr]),

    REMOVEFILTERS(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorName],

                  vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorArea],

                  vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorRegion],

                  vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorCountry]

        ),

    KEEPFILTERS(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Leave_Type] IN {"Holiday", "LeaveType"})

)

How to modify this dax so that i will get Leave_Hours in other rows (A, B, C, D). Here the problem is i need to populate the Leave_Hours in all rows.. the Leave_Hours (non-Billable = 16) are not being considered in the calculation.That's why iam getting wrong % in column 2.

Please note that "Standard_Hr1" is from a different table, but other values Billable_Hours , Leave_Hours are from the same table.

[GaloyanTelman]

Leave_Hours =
CALCULATE (
    SUM ( vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Time_In_Hr] ),
    REMOVEFILTERS (
        vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorName],
        vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorArea],
        vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorRegion],
        vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorCountry]
    ),
    ALLSELECTED ( vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Requestor] ),
    KEEPFILTERS ( vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Leave_Type]
        IN { "Holiday", "LeaveType" } )
)

[WinterGarden]

Hi @GaloyanTelman ,

This one was giving correct billable utilization values..
But was showing all the requestor names irrespective of the resource name.. Like requestors of other resources are also displaying in the matrix visual.

[krishnakanth240]

Hi @WinterGarden 

Can you provide the sample data of columns with the tables to work on the above DAX measures to check. Thank you!

[WinterGarden]

Hi @krishnakanth240 ,
This is the sample data, please note that i have two filters FiscalWeek and resource name..

FiscalWeek	Resource	Requestor	Billable	Leave_Type	Time_In_Hr
20	PB	A	Billable		9.3
21	PB	A	Billable		10.83
22	PB	A	Billable		6.75
23	PB	A	Billable		9.87
20	PB	B	Billable		6.23
21	PB	B	Billable		4.17
22	PB	B	Billable		4.7
23	PB	B	Billable		9.33
20	PB	C	Billable		8.28
21	PB	C	Billable		5.98
22	PB	C	Billable		5.62
23	PB	C	Billable		3.87
20	PB	D	Billable		1.62
21	PB	D	Billable		8.62
22	PB	D	Billable		3.31
23	PB	D	Billable		3
20	PB	Non-Billable	Non-Billable	NULL	0
21	PB	Non-Billable	Non-Billable	NULL	0
22	PB	Non-Billable	Non-Billable	LeaveType	16
23	PB	Non-Billable	Non-Billable	NULL	0

Standard_Hr1 is from a different table(vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data), that has many to many relationship with the table: vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User


Below is the sample data for standard hour:

FiscalWeek	Resource	stdd_hr
20	PB	40
21	PB	40
22	PB	40
23	PB	40

[krishnakanth240]

Hi @WinterGarden 

Thanks for sharing for one table.

Could you please share these inputs.

a)Some more data of records for these columns(Fiscal Week, Resource, stdd_hr) for 'Table' - vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data

b)Sample data of rows for these columns(SponsorName, SponsorArea, SponsorRegion, SponsorCountry) with records for the above 'Table' you shared - vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User

[WinterGarden]

Hi @krishnakanth240 ,

a) vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data : stdd_hr is same for all the resource.. 40 hrs per week

b) (SponsorName, SponsorArea, SponsorRegion, SponsorCountry):
AJT, APAC, OCEANIA, Australia - for all rows

[krishnakanth240]

Hi @WinterGarden 

Thanks for sharing!

Want to clarify from which table these columns are coming from 

Requestor

Billable_Utilization - As per my calculation

Billable_Hours

Standard_Hr1

Leave_Hours

It's confusing actually. Can you give all the list of table names along with the columns and 30 sample records for it to work on DAX. Thank You!

[WinterGarden]

Hi @krishnakanth240 ,

There are two tables
1)vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User:

FiscalWeek	Resource	Resource_GPN	Requestor	Billable	Leave_Type	Time_In_Hr	SponsorName	SponsorArea	SponsorRegion	SponsorCountry	Week_GPN
20	PB	PB01	A	Billable		9.3	AJT	APAC	OCEANIA	Australia	20PB01
21	PB	PB01	A	Billable		10.83	AJT	APAC	OCEANIA	Australia	21PB01
22	PB	PB01	A	Billable		6.75	AJT	APAC	OCEANIA	Australia	22PB01
23	PB	PB01	A	Billable		9.87	AJT	APAC	OCEANIA	Australia	23PB01
20	PB	PB01	B	Billable		6.23	AJT	APAC	OCEANIA	Australia	20PB01
21	PB	PB01	B	Billable		4.17	AJT	APAC	OCEANIA	Australia	21PB01
22	PB	PB01	B	Billable		4.7	AJT	APAC	OCEANIA	Australia	22PB01
23	PB	PB01	B	Billable		9.33	AJT	APAC	OCEANIA	Australia	23PB01
20	PB	PB01	C	Billable		8.28	AJT	APAC	OCEANIA	Australia	20PB01
21	PB	PB01	C	Billable		5.98	AJT	APAC	OCEANIA	Australia	21PB01
22	PB	PB01	C	Billable		5.62	AJT	APAC	OCEANIA	Australia	22PB01
23	PB	PB01	C	Billable		3.87	AJT	APAC	OCEANIA	Australia	23PB01
20	PB	PB01	D	Billable		1.62	AJT	APAC	OCEANIA	Australia	20PB01
21	PB	PB01	D	Billable		8.62	AJT	APAC	OCEANIA	Australia	21PB01
22	PB	PB01	D	Billable		3.31	AJT	APAC	OCEANIA	Australia	22PB01
23	PB	PB01	D	Billable		3	AJT	APAC	OCEANIA	Australia	23PB01
20	PB	PB01	Non-Billable	Non-Billable	NULL	0	AJT	APAC	OCEANIA	Australia	20PB01
21	PB	PB01	Non-Billable	Non-Billable	NULL	0	AJT	APAC	OCEANIA	Australia	21PB01
22	PB	PB01	Non-Billable	Non-Billable	LeaveType	16	AJT	APAC	OCEANIA	Australia	22PB01
23	PB	PB01	Non-Billable	Non-Billable	NULL	0	AJT	APAC	OCEANIA	Australia	23PB01

2)vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data

FiscalWeek	Resource	stdd_hr	Resource_GPN	Week_GPN
20	PB	40	PB01	20PB01
21	PB	40	PB01	21PB01
22	PB	40	PB01	22PB01
23	PB	40	PB01	23PB01

# Requestor - is from Table 1(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User)

# Billable_Utilization - DIVIDE([Billable_Hours],[Standard_Hr1]-[Leave_Hours])

# Billable_Hours = CALCULATE(SUM(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Time_In_Hr]),FILTER(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User,vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Billable]="Billable"))
------Billable_Hours is from Table 1(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User)-----

# Standard_Hr1 = sum(vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data[stdd_hr] )
----Standard_Hr1  is from Table 2(vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data)-----

# Leave_Hours =

CALCULATE(

    SUM(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Time_In_Hr]),

    REMOVEFILTERS(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorName],

                  vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorArea],

                  vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorRegion],

                  vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[SponsorCountry]

        ),

   KEEPFILTERS(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User[Leave_Type] IN {"Holiday", "LeaveType"})

)
---Leave_Hours is from Table 1(vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User)----

Also note that there is one - to - many relationship between Table 2 and Table 1 using "Week_GPN" column

[krishnakanth240]

Hi @WinterGarden 

Thanks for sharing!

Leave hours measure I have tried with sample data. It is working after adding Requestor column. Can you please try and confirm

[WinterGarden · Accepted solution]

@krishnakanth240 ,
Hi ,
This is giving correct billable utilization values, but  the problem is that other resources sponsors are also showing(The rows were Time_in_Hr and Billable_Utl columns are blank) since we removed the filters on the requestors.. 

Also the Non Billable_Utl should be blank for all requestors except Requestor = "Non- Billable" (it is showing 18.04% for all requestors..)

if i apply Time_In_Hr = is not blank on the filter pane of this matrix visual it will work.. But this Leave_Hour measure is causing issue in my another table viusual.. where all requestors are showing irrespective of the resource name..

So i created a duplicate of the table( vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User) where i filtered only the non billable rows(Billable column = Non-Billable) .. and updated the measures accordingly.. and it is working fine..

Thank you so much for all the suggestions 🙂 I really appreciate it.

[v-achippa]

Hi @WinterGarden,

Thank you @krishnakanth240 and @GaloyanTelman for the prompt response. 

Thank you for confirming that the issue is resolved now. Thank you for being part of Microsoft Fabric Community.

Thanks and regards,

Anjan Kumar Chippa

[krishnakanth240]

That's great to hear @WinterGarden it got solved.

If the approach has helped to meet requirement, please give a heads-up/accept as a solution. Thank you!