When filtering with the "Slicer" the state of the matrix, it performs erroneous calculations

[Syndicate_Admin]

Good afternoon community, How are you?

I ask for help with the next problem I have. I tell you, I have a database consisting of returned products that go through different stages identified with numbers through "0,1,2,3 and "Review"". The first thing I did was a measure that tells me all the returns through Cant. Dev = Count([State]), then I filtered each state through the calculate function for example:

Completed = CALCULATE([Dev quantity], [State] = "3").

As well as each representative number. Then create a new measure called Dev made% so you can calculate the % of completed returns whose code is:

Dev. made% = CALCULATE(DIVIDE([Completed], [Dev Quantity]))

The problem arises that when I have my matrix next to the slicer tool with "Status" filter and I want to filter by 0 instead of me getting that the % of dev completed is zero, I get 600%. Why could it be?

[v-tangjie-msft · Accepted solution]

Hi @Syndicate_Admin ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure.

Dev. made% = 
 var _a= SELECTEDVALUE('Table'[State])
 var _b= COUNTROWS(ALL('Table'))
var _c= CALCULATE(DIVIDE([Completed],_b))
 return 
 IF(_a="3",_c,0)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Syndicate_Admin]

Sorry, I forgot to clarify that:

0, 1, 2 and review are states that are not completed. That is, only state 3 indicates that the return was made. So, when I want to demonstrate the % of returns completed through a graph of needles, when filtering by 0, the new measure must give me 0 because if the measurement makes Dev. completed / total of dev it would be equal to 0 (because there should be no return completed from 0 to 2 including review) / x amount of returns and by dividing by 0, You should give me 0.

I hope you have been clearer and thank you very much for reponder!

[LQuedas]

Hey,

don't understand why you are saying that you are expecting  "% of dev completed is zero", when you are forcing the filter context of  the "Completed"  measure to have the state = 3. I did an example trying to replicate your situation, (if I understood it correctly :))... I create a table with different states:

then I've replicated your measures:

Completed = CALCULATE(DevTable[Dev Quantity], DevTable[State]=3)

Dev Quantity = count(DevTable[State])

but to calculate the "Dev made %", I used the expression:

Dev made % =

VAR TotalDev = CALCULATE(COUNT(DevTable[State]),all(DevTable))

Return CALCULATE(DIVIDE([Completed],TotalDev))

the resut is:

i.e. changing the slicer options the "Completed" and "Dev made %" are not affected.

Hope this help you/Guide you to your solution.

Cheers, LQ