Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
michael_knight
Post Prodigy
Post Prodigy

Count and % from an IF Statement/GENERATESERIES Slicer

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

 

1 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.  

View solution in original post

9 REPLIES 9
d_gosbell
Super User
Super User

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)

Yep, sorry about that. 

I made a GENERATESERIES 

Capture.PNG

I created this IF measure...

Capture1.PNG

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...

Capture2.PNG

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...

Capture3.PNG

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

 

Hope that makes more sense, @d_gosbell 

 

Thanks

Hi @michael_knight ,

 

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

8.PNG

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

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

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  ) )

Thanks for getting back to me, @d_gosbell 

 

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

Capture4.PNG

 

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.

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

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.  

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.