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

Sophie0320 — Mon, 13 Mar 2023 18:09:01 GMT

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 Power BI Community,

I created a dimension table to sort the rows.

After replacing the column Count Type in the report with Count Type (DIM), the rows are sorted. But two numbers are missing.

Anybody know the reason? How can I fix it?

Thanks a lot!

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

Sophie0320 — Thu, 16 Mar 2023 14:06:04 GMT

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?

topic Report view missing data issue after sorting rows by a custom order in Custom Visuals Development Discussion

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

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