Forum Discussion

Sophie0320's avatar
Sophie0320
Frequent Visitor
3 years ago

Report view missing data issue after sorting rows by a custom order

I have a report view like below. The expression of measure Q1_ is on the top, coming from the column Q1_pre.   By following Solved: How to sort my rows and columns by cutom order - Microsoft ...
Sophie0320's avatar
Sophie0320
Frequent Visitor
3 years ago

I have not figured our a solution. Let me explain more.

Here is the expression of the measure Q1_:

Q1_ =
var _A =
SWITCH (SELECTEDVALUE('Staffing Summary (2)'[Count Type]),
"% Completed", DIVIDE(CALCULATE(SUM('Staffing Summary (2)'[Q1_pre]), 'Staffing Summary (2)'[Count Type] = "Staffing Achieved"),
CALCULATE(SUM('Staffing Summary (2)'[Q1_pre]), 'Staffing Summary (2)'[Count Type] = "Planned Staffing"), 0),
"% Name Identified", DIVIDE(CALCULATE(SUM('Staffing Summary (2)'[Q1_pre]), 'Staffing Summary (2)'[Count Type] = "Names Identified"),
CALCULATE(SUM('Staffing Summary (2)'[Q1_pre]), 'Staffing Summary (2)'[Count Type] = "Planned Staffing"), 0),
SUMX('Staffing Summary (2)',[Q1_pre])
)
RETURN
switch (SELECTEDVALUE('Staffing Summary (2)'[Count Type]), "% Completed", FORMAT(_A,"#%"), "% Name Identified", FORMAT(_A,"#%"),FORMAT(_A, "#,0"))

I need to recalculate the two % rows because they cannot simply sum up the value in each Resource Category, like what Q1_pre does. 

The weird thing is 100% and 50% are gone after I sorted the rows by using the sorted column Count Type (DIM) in the index table. Does anybody know what happened?