Count and % from an IF Statement/GENERATESERIES Slicer

[michael_knight]

Hi,

At a bit of a dead end. I've create an IF statement that works with a GENERATESERIES measure, and that works fine in a table visual and is fluid with the slicer, however I want a count and a % of each the value from the IF statement that also works and is fuild with the GENERATESERIES slicer

Hope someone can help

Thanks

[d_gosbell]

I've read through this question a couple of times and I'm struggling to both understand what your measures currently look like and what results you are after.

Are you able to provide a small set of example data and the code for your existing measures?

(see How to Get Your Question Answered Quickly - Microsoft Power BI Community for the sort of information that helps us answer questions quickly)

[michael_knight]

Yep, sorry about that. 

I made a GENERATESERIES 

I created this IF measure...

And I use this measure in a table to display if the Target is under the % then return "Under", if over the % then return "Over". Like so...

This works fine and it changes if I change the target value. However, I want to display a count and a % of the Over and Under, like this...

But I want the Over and Under to change if I adjust the target value 

Hope that makes more sense, @d_gosbell 

Thanks

[v-diye-msft]

Hi @michael_knight ,

Let me know if you'd like to get below result, pbix attached.

Note that you didn't define the return value if [Round]=target. 

[d_gosbell]

OK, I can sort of see what you are doing now. Without knowing table and column names I can't be exact, but a pattern like the following should work:

Over Count = CALCULATE( COUNTROWS( table1 ), KEEPFILTERS(table1[Value] > Target[Target Value] ) )

Over % = DIVIDE( [Over Count] , COUNTROWS( table1  ) )

[michael_knight]

Thanks for getting back to me, @d_gosbell 

I gave it a go and it didn't like it

[d_gosbell]

That would imply that [Round] is a measure not a column. You can't use a measure in that sort of pattern. Like I said, it's really hard to suggest calculations without knowing a bit more about what your data look like.

So what is this Round measure and what is it doing? Can you just use the base column that this measure is using to do the filtering? If you could follow the suggestions in the link I posted earlier it would enable us to get to a solution much faster.

[michael_knight]

I didn't use a measure, I used a column. The Round column is Number 1/Number2 * 100

I'll attach my PBIX file so you can have a look if you want. 

Also @v-diye-msft I thanks for the post. I gave it a go in the PBIX file. From what I can see, it doesn't include the any from the =100 statement. I am also getting an error in the table whenever I interact with anything in the table

Thanks for the help so far, guys. I do appriciate it. 

https://www.dropbox.com/s/zg9lxyntnm81q0j/Market%20Value.pbix?dl=0

[d_gosbell · Accepted solution]

Hmm, it seems to be the target value measure causing the issue. However if you inline the call to selectedvalue() it works fine.

Over Count = 
VAR _target = SELECTEDVALUE(Target[Target],105)
return  CALCULATE(countrows('Acqui Offer'), 'Acqui Offer'[Round] > _target )

Note: I am counting the rows in 'Acqui Offer' - you could possibly make it more efficient by counting VALUES( new_acquioffer_id). I don't think counting the distinct values of the round column is correct as there are a few rows which have the same value for round.  

[michael_knight]

Sweet, progress. However, it's not reconising the 100% values when I make the Target 100. 

Also, do you get the error on your end when interacting with the table? 

Thanks,

Mike