Matrix two level calculation

[darko861]

Hi all,

I have a matrix, with two rows displaying the Member's names (which is blurred out) and their Internal or Customer time. The data model consists of the Members' table (The Members' names come from this table) and a fact table called V_EIS_Comined... (There is a column here called InternalorCustomer which categorizes each row as either Internal or Customer ).

I have Five measures:

Worked Hours = SUM(v_EIS_Combined_Employee_Company_Time2[Registered_Hours])

Billed Hours = 

IF(SUM(v_EIS_Combined_Employee_Company_Time2[Billed_Hours])=0,BLANK(),SUM(v_EIS_Combined_Employee_Company_Time2[Billed_Hours]))

Targeted Hours = 

SWITCH(TRUE(),SELECTEDVALUE(v_EIS_Combined_Employee_Company_Time2[InternalorCustomer]) = "Customer Time",SUM(Budgeted_Hours[Value]),BLANK())

Targeted Hours2 = SUMX(SUMMARIZE(v_EIS_Combined_Employee_Company_Time2,v_EIS_Combined_Employee_Company_Time2[InternalorCustomer],v_rpt_Member[Member_Full_Name]),[Targeted Hours])

Diff Hours = 

SWITCH(TRUE(),SELECTEDVALUE(v_EIS_Combined_Employee_Company_Time2[InternalorCustomer]) = "Customer Time",[Billed Hours] -[Targeted Hours],BLANK())

I have two members in the Matrix, The first member has Worked hours for the month of Feb-2023 and the last member has no worked hours for Feb-2023. The Matrix works perfectly for the first member. But the last member has still targeted hours for the month of Feb-2023. Unfortunately, you cannot see those hours in the measure Targeted Hours2, because of the syntax in Targeted Hours 2.

So, I tried to tweak the code a bit, and write a new measure called Targeted Hours5:

Targeted Hours5 = SWITCH(TRUE(),SELECTEDVALUE(v_EIS_Combined_Employee_Company_Time2[InternalorCustomer]) = "Customer Time",SUM(Budgeted_Hours[Value]),

SELECTEDVALUE(v_EIS_Combined_Employee_Company_Time2[InternalorCustomer])=BLANK(),SUM(Budgeted_Hours[Value]),BLANK())

This does now include the correct targeted hours for the first and last members. But for the last member, I would like to display only the targeted hours in the row where it says Customer Time and hide the hours in the row Internal Time. So, 159 should only be displayed at the customer time level and internal time should be blank (at the moment it says 159 also, please see print screen below). Is this possible when the last member has no time worked in Feb-2023? How can I tweak the dax measure Targeted Hours5?

 

[v-rzhou-msft]

Hi @darko861 ,

Your calculation is based on your data model, please share a sample file and show me a screenshot with the result you want. This will make us easier to find the solution.

Best Regards,
Rico Zhou

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

[darko861]

Hi @v-rzhou-msft 

Here is the sample file.

https://www.dropbox.com/s/jfyd9qzqa5qb9hc/Test%20-%20Copy.pbix?dl=0

I would like to have blank values for e.g. Customer 276 and Customer 157 where the row is Internal Time and the column is Targeted Hours5.

At the moment Customer 276 has 142 hours (Jan) and 159 hours (Feb) in the row Internal Time.

Customer 157 has 0 hours (Jan & Feb) in the row Internal Time. But this is redundant as the Targeted Hours should only be visible in the Customer Time row and not in the Internal Time row.

It should look like customer 200, where the targeted hours only appear at the Customer Time level.

I appreciate the help 🙂