This is AWESOME! Thanks Patrick for teaching us all the little tricks and tips to make us better employee and resourceful employee at work!! Thumbs UP!
Great video, thanks for the tips! If you want the _bothways arrow to go up and down instead of left and right, change the UNICHAR (8660) to UNICHAR (8661).
Great solution. My struggle with it is that the visualization for -6% and -60% are identical, so while it's "visual" you lose the ability to interpret scale. What if you provided something like 1-4 arrows for the up and down arrows based on range bins of % growth or shrinkage?
1] What about using a graphic instead of a text character for the symbols? What about relative sizes where the symbols for more positive or negative are larger than those for less positive or negative. 2] That tool tip is HUGE; can it be made more discrete? How about a user-toggle to dynamically switch the matrix between symbols and values? 3] Is there any ways to show an 'expand/contract' symbol beside the rows for years, like we have in Pivot tables in Excel, instead of the full-table drill-down buttons on the top-right?
Check out the UA-cam video DAX 101 and Advanced DAX by Alberto Ferrari. A must-see if you're serious about DAX. After that, attend one of his or Marco's courses!
Loved your video, watched 15 videos on creating a scorecard in PowerBI and this one is the best I encountered. I'm curious is there a corresponding function in MDX for showing a symbol like this? I'm using an SSAS datacube with live stream of data that is updated every 15 minutes.
What a coincidence: I was importing today an Excel File with KPIs inside and using the Status for Conditional formatting. I will have to re-build it now with DAX ;) and I'll give it a try with a disconnected table for thresholds
Great video Patrick! Thanks for sharing! I've tried to play with that in my reports. Do you have any idea why color is not working in subtotals and grand totals in matrix?
Nice Video Pat. Can we do the same when we have calculated measures as the KPI instead of a table and we wanna add conditional formatting to it? Thanks
Hi patrick, thanks for another cool video! I've been using this technique for a while but what I encounter is that the colouring only works on the row level. For some reason conditional formatting doesn't work on the total level or on cards. Do you have a workaround for this? Or do you know why microsoft hasn't enabled this?
Hey Patrick...greetings from the Philippines! have a problem with conditional formatting. Say, need to format as green if the value is above target and red if bellow target. The problem is, these targets changes per day and i dont have the 'raw' data as the client only gives us the numbers/actual targets. How do I reference it with my table showing the scores per day? This is pretty easy to do in excel but i have been struggling to figure out how to do it in power bi
Hi, Is it possible to show a Pie chart or a Line Chart in place of arrow symbols? I am thinking of a KPI scorecard for Sales where we can show Sales Agents in rows and KPIs in Columns. Instead of showing values or the arrows, it will be really helpful to show a pie chart (I am assuming KPI %) as a multi row card. Please advice whether it’s doable.
Not sure if this is what you are looking for, but a while back Patrick did a video on creating a custom KPI card that had a pie chart incorporated into it. Maybe you could steal some techniques from that video? ua-cam.com/video/zHzSBNES6jE/v-deo.html
I guess there is a simple way to do this without using so long DAX (always scared of long DAX). why cant we calculate % change and use the same % change to show with unichar
Just append something like this, &FORMAT([YOY Growth], " ##.00%"), to the end of the KPI Indicator Measure. Meant to show this in the video. I may do a short one of this.
Guy in a Cube Thank you. I do a lot of scorecard metrics and I am definitely going to test these tricks. Do you think these will work with excel query tool or only power bi?
@@Ready_Set_Boom They have started working on delivering the icon sets in PBI similar to the conditional formating icons we have Excel already so hopefully you won't have to be using these tricks for long.
Hi, can you help me figure out how I can write this VAR formula to use the _YellowStatus twice so I can get rid of the double pipe symbol? VAR _RedStatus = -3 VAR _YellowStatus = -1 || -2 VAR _GreenStatus = 0 I basically need to write out YellowStatus twice, once for -1 and another for -2
I answered my own question after watching the video 10 more times. I wrote it pretty much like he did at this point VAR _Bottomthreshold = -2 VAR _Topthreshold = -1 RETURN SWITCH( TRUE(), [Days to Target] < _Bottomthreshold, -1, [Days to Target] > _Topthreshold, 1, [Days to Target] >= _Bottomthreshold && [Days to Target]
Hi Patrick I have LIFR Data where i have 3 columns - Total line count, Total missed Line and result is LIFR %. If 100 % its superb. I have LIFR target for each country, for below targets i want to set KPI indicators, colors, status etc. Can u guide me on this. My columns are country, code, week (last 4 weeks data), LIFR count, LIFR missed and LIFR %. please guide me.
ooooooo, Whats Up Guys in a Cube. Please Could you help me? I have 5 report and 1 data for each report. public 5 reports 10mb each. 50MB in my workspace. or I public 1 dataset 10mb and connect my reports in dataset and each report have 500kb, now my workspace use 12,5mb Who is better in performance?
Simple, elegant, to the point and can be used in a myriad of scenarios!! Thank you very much for these solutions!
This is AWESOME! Thanks Patrick for teaching us all the little tricks and tips to make us better employee and resourceful employee at work!! Thumbs UP!
This was so good. So much useful tips and tricks here. Thanks!
Great video, thanks for the tips! If you want the _bothways arrow to go up and down instead of left and right, change the UNICHAR (8660) to UNICHAR (8661).
As always (let's say most of the times)... simple...sooo helpful.
I just love you guys
Thanks for that! 👊
the best video of KPIS
Really thanks Patrick!! You are able to simplify complexity!!!
Hi Patrick, I love the way you explain!! thanks
Thanks for this video, Patrick. Really elegant and simple to implement.
Really great stuff - easy to follow and such useful info!
Love it! Thanks Anant! 👊
You're genius and I like your speech ! This is bananas ^^
One word. GENIUS
Guys, you keep blowing our minds!!
You bet! Glad you liked it. 👊
Great solution. My struggle with it is that the visualization for -6% and -60% are identical, so while it's "visual" you lose the ability to interpret scale. What if you provided something like 1-4 arrows for the up and down arrows based on range bins of % growth or shrinkage?
Really smart way of doing this. Thank you.
Awesome Patrick
I really like the simplicity of developing the kpis..
Nice one... Im gonna try it this week!
1] What about using a graphic instead of a text character for the symbols? What about relative sizes where the symbols for more positive or negative are larger than those for less positive or negative.
2] That tool tip is HUGE; can it be made more discrete? How about a user-toggle to dynamically switch the matrix between symbols and values?
3] Is there any ways to show an 'expand/contract' symbol beside the rows for years, like we have in Pivot tables in Excel, instead of the full-table drill-down buttons on the top-right?
Wow, good timing, I was wondering how to make a similar matrix! You guys rock! Any specific way to learn the DAX features for a rookie like me?
Check out the UA-cam video DAX 101 and Advanced DAX by Alberto Ferrari. A must-see if you're serious about DAX. After that, attend one of his or Marco's courses!
Loved your video, watched 15 videos on creating a scorecard in PowerBI and this one is the best I encountered. I'm curious is there a corresponding function in MDX for showing a symbol like this? I'm using an SSAS datacube with live stream of data that is updated every 15 minutes.
What a coincidence: I was importing today an Excel File with KPIs inside and using the Status for Conditional formatting.
I will have to re-build it now with DAX ;) and I'll give it a try with a disconnected table for thresholds
Is this basically the more advanced DAX version of going to your value and right clicking Conditional Formatting with Icons? For more control?
Great video Patrick! Thanks for sharing!
I've tried to play with that in my reports. Do you have any idea why color is not working in subtotals and grand totals in matrix?
Very nice video. Is it possible to take bottom and top threshold as an input from dashboard users? Like a slider of what if?
This is amazing. Thanks Dude
Most welcome! Thanks for watching! 👊
Nice video! What about having this same KPIs in a TOTAL or SUBTOTAL, I didn't get working the conditional formatting there...
Nice Video Pat. Can we do the same when we have calculated measures as the KPI instead of a table and we wanna add conditional formatting to it? Thanks
This is awesome, thank you for this
Great video, very helpful. Thanks!
Just nice and simple
Hello, I love your videos. Can you recommend a DAX programming book for beginners?
Thank you, so much! You're a genius
Auto-generated captions at 0:55. Gave me a chuckle!!😂
sigh. yeah. will be great once we get the non-auto-generated captions going. takes some $$$ though :(
Hi patrick, thanks for another cool video! I've been using this technique for a while but what I encounter is that the colouring only works on the row level. For some reason conditional formatting doesn't work on the total level or on cards.
Do you have a workaround for this? Or do you know why microsoft hasn't enabled this?
Is it possible to add a nicer arrow (like using a shape or Picture) to make the visual better looking?
Hi Patrick.
This is very cool feature that you shared with us.
Could you advise where I can get your video PBIX file?
Thank you.
I found it and thank you
Hi Patrick. Great videos. Can you please tell me which software are you using to record screen and voice ?
Adam did a video on his techniques earlier in 2019. ua-cam.com/video/sTI__ywg8I8/v-deo.html
Hi. Nice video. where can I donload the pbix? Thanks.
This is a cool feature
Agreed! A nice little trick.
great video. Please how to get data from firebase to power BI? Thks
Hey Patrick...greetings from the Philippines!
have a problem with conditional formatting. Say, need to format as green if the value is above target and red if bellow target. The problem is, these targets changes per day and i dont have the 'raw' data as the client only gives us the numbers/actual targets. How do I reference it with my table showing the scores per day? This is pretty easy to do in excel but i have been struggling to figure out how to do it in power bi
Awesome!! Thank you!! 👍👍
thank you so much, please can i change the chart colot using some conditions ? means that the color of the chart change in terms of condition
Thanks for helpful video
Most welcome Kenny! Thanks for watching. 👊
Please advise how can we calculate difference of 2 cards
Hi, Is it possible to show a Pie chart or a Line Chart in place of arrow symbols? I am thinking of a KPI scorecard for Sales where we can show Sales Agents in rows and KPIs in Columns. Instead of showing values or the arrows, it will be really helpful to show a pie chart (I am assuming KPI %) as a multi row card. Please advice whether it’s doable.
Not sure if this is what you are looking for, but a while back Patrick did a video on creating a custom KPI card that had a pie chart incorporated into it. Maybe you could steal some techniques from that video?
ua-cam.com/video/zHzSBNES6jE/v-deo.html
Love it!
I guess there is a simple way to do this without using so long DAX (always scared of long DAX). why cant we calculate % change and use the same % change to show with unichar
Now we just need to combine the value and the symbol so we see both in the same cell.
Just append something like this, &FORMAT([YOY Growth], " ##.00%"), to the end of the KPI Indicator Measure. Meant to show this in the video. I may do a short one of this.
Guy in a Cube
Thank you. I do a lot of scorecard metrics and I am definitely going to test these tricks.
Do you think these will work with excel query tool or only power bi?
@@Ready_Set_Boom They have started working on delivering the icon sets in PBI similar to the conditional formating icons we have Excel already so hopefully you won't have to be using these tricks for long.
@@d3x0x any eta on this
@@GuyInACube Please please show us this in a video
How do I create a scorecard that has multiple categories and sub-categories? Similar to the screenshot below?
never ever done this but i'd love to learn how to do this at work
Give it a try. There are a lot of visual tricks like this can can help.
Hi, can you help me figure out how I can write this VAR formula to use the _YellowStatus twice so I can get rid of the double pipe symbol?
VAR _RedStatus = -3
VAR _YellowStatus = -1 || -2
VAR _GreenStatus = 0
I basically need to write out YellowStatus twice, once for -1 and another for -2
I answered my own question after watching the video 10 more times. I wrote it pretty much like he did at this point
VAR _Bottomthreshold = -2
VAR _Topthreshold = -1
RETURN
SWITCH(
TRUE(),
[Days to Target] < _Bottomthreshold, -1,
[Days to Target] > _Topthreshold, 1,
[Days to Target] >= _Bottomthreshold && [Days to Target]
Hi Patrick I have LIFR Data where i have 3 columns - Total line count, Total missed Line and result is LIFR %. If 100 % its superb. I have LIFR target for each country, for below targets i want to set KPI indicators, colors, status etc. Can u guide me on this. My columns are country, code, week (last 4 weeks data), LIFR count, LIFR missed and LIFR %. please guide me.
I have not could find the country items on the file. There is no any country on it. Can anyone help me?
Hi patrick, I created a measure and it immediately dissappeared :( what am i doing wrong?
Is there a way to change unchar size?
I have a report published in public, Is there any way to get what user have selected in search dropdowns? @/guyinacube
ooooooo, Whats Up Guys in a Cube.
Please Could you help me?
I have 5 report and 1 data for each report. public 5 reports 10mb each. 50MB in my workspace.
or I public 1 dataset 10mb and connect my reports in dataset and each report have 500kb, now my workspace use 12,5mb
Who is better in performance?
mind = BLOWN
Love it! Thanks for watching Luca 👊
great!!!!!
Ace!!
Never mind. Just found it. :-)
DAX rules
but yo i need a little numbs.