Incorrect measure grand tot

[DianaF]

Dear all,

First, I would like to thank this amazing community for all the help you have provided me so far! Only recently have I started using DAX for in depth analysis and I have learned so many things from here!

Secondly, I have encountered an issue with the grand total of a measure which is not being reflected correctly. I will provide more details:

• Table 1 (FACT table): contains a table with a field where I have the total hours worked by an employee on a project (1 employee can have multiple project, hence to why I have duplicates on the Employee ID). I call it WORKING_HOURS;
• Table 2 (DIM table): contains a table made of 2 columns: 1 column represents the work location of the employee and the other column represents the contractual working hours per day. The work locations are unique. I call it HOURS_PER_DAY
• These tables have an active relationship based on the work location, cardinality many (FACT) to one (DIM), cross filter direction: both
• Below I am posting dummy data for an easier understanding:

Fact Table. Employee ID	Fact table. WORKING_HOURS	Dim Table. HOURS_PER_DAY	Measure: WORKING_DAYS
1	168	8	168/8 = 21
2	150	8	18.75
3	140	7.5	18.66
4	130	9	14.44
5	120	8	15
6	110	7.5	14.66
GRAND TOTAL	818	48 - irrelevant total	The grand total here is: 818/48 = 17.04, but this is not correct. It should be the total of the values above: 21 + 18.75 + ... + 14.66 = 102.51

What I want to achieve: I want the Grand Total to calculate the sum of the column. Now, it`s been almost a week since I tried to work this out and I`ve read a lot of solutions online, which did not work, I am definetly doing something wrong 😢 The latest version of my measure is as follows:

1. First I create a measure to do the division between WORKING_HOURS and HOURS_PER_DAY: 
   • Measure 1 = DIVIDE (SUM ( Fact table. WORKING_HOURS ), SUM (Dim table.HOURS_PER_DAY) ) - 
   • The output is as above, shows correctly
   • This is only a curiosity, but when I try to use SUMX formula with HOURS_PER_DAY, it is not letting me... the field does not appear at all!
2. Second, I looked up online I understood that the above formula works well for the rows, as they are calculated one by one as they have their own filter context, the total row has no filter context. So I wanted to create an IF formula:
   • Logic test: check if there is a row filter applied - ISFILTERED ( `Fact Table`
   • Value if true: Measure 1 output (which would basically show correctly) 
   • Value if false: calculate the total of all values from the rows at measure 1: CALCULATE ( SUMX (VALUES (FACT TABLE ), Measure 1, ALL SELECTED (FACTTABLE) )

I think that what is messing up, is the FACT & DIM tables, perhaps I am not specifiying them correctly in the formula? I am not sure really, but I have a feeling that I confused DAX with the tables, hence to why I have presented them in the beginning.

P.S. What I am trying to achieve was done in Power Query, but I want to do this in DAX as I want to expand the knowledge 😥

[amitchandak]

@DianaF , You can try like

sumx(summarize(Table, Table[Employee ID],"_1",[Measure 1 ]),[_1])

[parry2k · Accepted solution]

@DianaF try this measure

Measure 2 = 
SUMX ( VALUES ( Table[Employee Id] ), [Measure 1] )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[DianaF]

Hello sir, thank you for this. It worked. Have a lovely day!