skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Getting started
      • Overview
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power BI Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish Community
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Data Stories Gallery
    • Re: Sales Scorecard: Where are we losing money? by...

    Re: Sales Scorecard: Where are we losing money? by Decisive Data

    10-04-2018 01:57 AM

    Guidotreur
    Frequent Visitor
    35313 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Sales Scorecard: Where are we losing money? by Decisive Data

    ‎12-20-2016 12:52 PM

    Summary

     

    "How is my company doing?"

     

    It's a question we hear often. It seems simple enough, but answering it is a complex process. This report is designed to answer the question in a clear and straightforward manner, while providing enough detail to take some action.

     

    If we wanted to further develop this story, we would build on the outcomes of our Sales Scorecard by adding additional reports with more granular data. Each of these report tabs would answer one specific question that arises from use of our Sales Scorecard.

     

      

     

    Approach

     

    A report is most valuable when it's actionable. We started by identifying which components of the process we can influence:

    • What we're selling (products)
    • Where we're selling (region, state)
    • When we're selling (month, year)
    • Who is doing the selling (salesperson)

    We then determined the report's focus: areas that are not performing well. We used color to highlight these problem areas, and leveraged a matrix with conditional formatting to give us granular insights that could prompt strategy changes. Finally, we included comparisons of profit and sales against prior years to put the performance into perspective. We leveraged basic Power BI elements (cards, shapes, tree maps, and DAX) to develop a KPI section in the top right of these charts to provide additional context.

     

    From this report it's clear that we should evaluate our training programs. We may want to pick up the phone and follow up with William, who seems to be struggling in several areas, most notably in OSHA supplies which is a profitable area for all other sales staff.

     

    We might also want to look further into the strange profit behavior in Mar 2013, where we saw a large dip compared to the prior year.

     

    A report like this is the first step into answering the question "How is my company doing?"

     
    Update 4/12/2017: You can use the DAX UNICHAR function to do this more simply. See the update section below. 

     

     

     

    Pushing the Boundaries

     

    Every so often you have a clear vision of how to communicate something, but you aren't quite able to realize that vision with the default tools or options. Custom visuals is one method to help realize your vision, but I'm not a D3 expert and I like a challenge, so sometimes I'll attempt to repurpose default tools in non-standard ways to achieve the same effect.

     

    I had a vision for what I wanted my KPI visualization to look like:

      

    The main elements are:IMG_4538.JPG

    • % Growth Year over Year (main value)
    • Current Year value (adds context)
    • Prior Year value (adds context)
    • Indicator (quick recognition of good/bad state)

     

    I started with a card for my main value, the % Growth Year over Year. I then created 2 dax measures to format my Current Year and Prior Year values nicely for display (using FORMAT to add "CY: " or "PY: " in front of the value for context). I put each of those into separate cards and arranged them next to my % Growth card.

     

    Finally, I created the indicators. I made a triangle shape, colored it grey, and oriented it upwards. I made a second triangle shape, colored it red, and oriented it downwards, and aligned it directly under the upward triangle. I then created 2 dax measures called KPI Red and KPI Grey.

     

    KPI Red = 1 when % Growth is Positive and 0 when % Growth is Negative.

    KPI Grey = 0 when % Growth is Positive and 1 when % Growth is Negative.

     

    I then put each of those into individual tree maps, changed the data colors to match the background, and arranged the tree maps over the tops of the triangles. Now when % Growth is negative, KPI Red is 0 and the tree map disappears and shows the red triangle underneath, while KPI Greyis 1 and the tree map covers the grey arrow.

     

    KPI2.PNG

     

     

     

    Wrapping it up

     

    The method above worked pretty well and got me what I wanted. However there are a few oddities - light white lines that outline the tree maps, the arrows are in different locations if they are up or down (because they're 2 different triangles), and the CY and PY are centered rather than left aligned since they're in cards.

     

    Ultimately, this got us where we needed to go for now. However, as a next step I would begin investigating how to turn this into a custom visual to make future use much simpler. For now, it's great to know that we can leverage basic Power BI elements like triangles to create custom KPIs. It just takes a bit of thinking outside the box, plus some creative use of DAX and willingness to use standard visuals (tree maps) in non-standard ways (covers to hide something).

     

     

      

    4/12 Update: Simplify using UNICHAR

     

    I just discovered the UNICHAR function in DAX, and used this to simplify my process! Currently we have 2 elements for each indicator, for a total of 8 elements. This method cuts the elements in half!

     

    1. Delete the tree maps and the arrow objects
    2. Delete the KPI Red/Green measures for Sales and Profit (4 total)
    3. Create 4 new measures
      • Sales Indicator Positive = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Sales YoY Growth]>0),UNICHAR(11205),"")
      • Sales Indicator Negative = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Sales YoY Growth]<0),UNICHAR(11206),"")
      • Profit Indicator Negative = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Profit YoY Growth]<0),UNICHAR(11205),"")
      • Profit Indicator Positive = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Profit YoY Growth]>0),UNICHAR(11206),"")
    4. Put each measure in a separate card.
    5. Change the Data Label colors to make the down arrows red and the up arrows grey
    6. Arrange your arrows and you're done!

    With this method, we are using the UNICHAR to display UNICODE arrow characters. Since we're doing this in DAX, we can us IF statements to hide/show the arrows based on our measures, which means we no longer need to use the tree maps as covers, cutting our number of elements in half! Less elements = easier to maintain.

     

    Enjoy!

     

     

    Jared Knutzen | Visualization Consultant

    Decisive Data | www.decisivedata.net

    DDlogo-small.png

     

     

    eyJrIjoiZjQ0NWYwYzEtYjE1OC00MWExLWEwYjgtMGMwZmFmZTZiYTUyIiwidCI6IjdlY2M4YWEwLTgzMGUtNGQ4Ni1hZjc4LTgzYTNkY2MyNjIzOCIsImMiOjZ9

    Preview file
    199 KB
    Labels:
    • Labels:
    • Business
    • Other
    Message 1 of 85
    153,326 Views
    97
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    Poetae_
    Poetae_
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-17-2021 01:55 PM

    Nice

     Can you help me with the .pbix file, I really appreciated if you could send me via email chavez.aact@gmail.com

     

    Thanks! 😄

    Message 44 of 85
    21,597 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-26-2021 09:04 AM

    Great Visualization! Can you help me with the .pbix file, I really appreciated if you could send me via email tingibson@gmail.com 
    Thanks!

    Message 43 of 85
    22,990 Views
    0
    Reply
    PallaviMandal07
    PallaviMandal07
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-15-2021 01:26 PM

    This is outstanding DB.  Would you kindly share the pbix & data files to pallavimandal.1911@gmail.com 

    Many thanks in advance!

    Message 42 of 85
    33,032 Views
    0
    Reply
    Gjorgh83
    Gjorgh83
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-07-2021 08:24 AM

    @Anonymous wrote:

    Summary

     

    "How is my company doing?"

     

    It's a question we hear often. It seems simple enough, but answering it is a complex process. This report is designed to answer the question in a clear and straightforward manner, while providing enough detail to take some action.

     

    If we wanted to further develop this story, we would build on the outcomes of our Sales Scorecard by adding additional reports with more granular data. Each of these report tabs would answer one specific question that arises from use of our Sales Scorecard.

     

      

     

    Approach

     

    A report is most valuable when it's actionable. We started by identifying which components of the process we can influence:

    • What we're selling (products)
    • Where we're selling (region, state)
    • When we're selling (month, year)
    • Who is doing the selling (salesperson)

    We then determined the report's focus: areas that are not performing well. We used color to highlight these problem areas, and leveraged a matrix with conditional formatting to give us granular insights that could prompt strategy changes. Finally, we included comparisons of profit and sales against prior years to put the performance into perspective. We leveraged basic Power BI elements (cards, shapes, tree maps, and DAX) to develop a KPI section in the top right of these charts to provide additional context.

     

    From this report it's clear that we should evaluate our training programs. We may want to pick up the phone and follow up with William, who seems to be struggling in several areas, most notably in OSHA supplies which is a profitable area for all other sales staff.

     

    We might also want to look further into the strange profit behavior in Mar 2013, where we saw a large dip compared to the prior year.

     

    A report like this is the first step into answering the question "How is my company doing?"

     
    Update 4/12/2017: You can use the DAX UNICHAR function to do this more simply. See the update section below. 

     

     

     

    Pushing the Boundaries

     

    Every so often you have a clear vision of how to communicate something, but you aren't quite able to realize that vision with the default tools or options. Custom visuals is one method to help realize your vision, but I'm not a D3 expert and I like a challenge, so sometimes I'll attempt to repurpose default tools in non-standard ways to achieve the same effect.

     

    I had a vision for what I wanted my KPI visualization to look like:

      

    The main elements are:IMG_4538.JPG

    • % Growth Year over Year (main value)
    • Current Year value (adds context)
    • Prior Year value (adds context)
    • Indicator (quick recognition of good/bad state)

     

    I started with a card for my main value, the % Growth Year over Year. I then created 2 dax measures to format my Current Year and Prior Year values nicely for display (using FORMAT to add "CY: " or "PY: " in front of the value for context). I put each of those into separate cards and arranged them next to my % Growth card.

     

    Finally, I created the indicators. I made a triangle shape, colored it grey, and oriented it upwards. I made a second triangle shape, colored it red, and oriented it downwards, and aligned it directly under the upward triangle. I then created 2 dax measures called KPI Red and KPI Grey.

     

    KPI Red = 1 when % Growth is Positive and 0 when % Growth is Negative.

    KPI Grey = 0 when % Growth is Positive and 1 when % Growth is Negative.

     

    I then put each of those into individual tree maps, changed the data colors to match the background, and arranged the tree maps over the tops of the triangles. Now when % Growth is negative, KPI Red is 0 and the tree map disappears and shows the red triangle underneath, while KPI Greyis 1 and the tree map covers the grey arrow.

     

    KPI2.PNG

     

     

     

    Wrapping it up

     

    The method above worked pretty well and got me what I wanted. However there are a few oddities - light white lines that outline the tree maps, the arrows are in different locations if they are up or down (because they're 2 different triangles), and the CY and PY are centered rather than left aligned since they're in cards.

     

    Ultimately, this got us where we needed to go for now. However, as a next step I would begin investigating how to turn this into a custom visual to make future use much simpler. For now, it's great to know that we can leverage basic Power BI elements like triangles to create custom KPIs. It just takes a bit of thinking outside the box, plus some creative use of DAX and willingness to use standard visuals (tree maps) in non-standard ways (covers to hide something).

     

     

      

    4/12 Update: Simplify using UNICHAR

     

    I just discovered the UNICHAR function in DAX, and used this to simplify my process! Currently we have 2 elements for each indicator, for a total of 8 elements. This method cuts the elements in half!

     

    1. Delete the tree maps and the arrow objects
    2. Delete the KPI Red/Green measures for Sales and Profit (4 total)
    3. Create 4 new measures
      • Sales Indicator Positive = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Sales YoY Growth]>0),UNICHAR(11205),"")
      • Sales Indicator Negative = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Sales YoY Growth]<0),UNICHAR(11206),"")
      • Profit Indicator Negative = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Profit YoY Growth]<0),UNICHAR(11205),"")
      • Profit Indicator Positive = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Profit YoY Growth]>0),UNICHAR(11206),"")
    4. Put each measure in a separate card.
    5. Change the Data Label colors to make the down arrows red and the up arrows grey
    6. Arrange your arrows and you're done!

    With this method, we are using the UNICHAR to display UNICODE arrow characters. Since we're doing this in DAX, we can us IF statements to hide/show the arrows based on our measures, which means we no longer need to use the tree maps as covers, cutting our number of elements in half! Less elements = easier to maintain.

     

    Enjoy!

     

     

    Jared Knutzen | Visualization Consultant

    Decisive Data | www.decisivedata.net

    DDlogo-small.png

     

     

    eyJrIjoiZjQ0NWYwYzEtYjE1OC00MWExLWEwYjgtMGMwZmFmZTZiYTUyIiwidCI6IjdlY2M4YWEwLTgzMGUtNGQ4Ni1hZjc4LTgzYTNkY2MyNjIzOCIsImMiOjZ9


    Hello Jared,

     

    Would you mind sharing the Pbix file for this report with me? my email is gjorgez@gmail.com

    Message 40 of 85
    33,490 Views
    0
    Reply
    Gaolili
    Gaolili
    Frequent Visitor
    In response to Gjorgh83
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-08-2021 03:36 AM

    Very impressive and I want to learn how to make it. can you share the pbix file with me? liga810@gmail.com

    Message 41 of 85
    33,314 Views
    0
    Reply
    ajithkumar2k
    ajithkumar2k
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-06-2021 05:39 AM

    This is a very impressive dashboard and has been very helpful for me while making a functional dashboard for business insight. Thanks again for sharing your experience. One of the areas I am stuck with is YOY%. I would appreciate if you can share the pbix file at ajithkumar2k@gmail.com?

    Message 39 of 85
    37,513 Views
    0
    Reply
    ChrisEzard
    ChrisEzard
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎02-25-2021 05:28 AM

    I really like what you've done with this. Asking and answering questions with few colours making it easier to focus on the information. The trend explanation blocks are very helpful. However, the Profit Trend arrows are the wrong way round, which is confusing. Increased profit has a red downwards arrow instead of a grey upwards arrow, easily fixed in your measures.

    Message 38 of 85
    38,204 Views
    0
    Reply
    lovishsood1
    lovishsood1 Helper I
    Helper I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎02-24-2021 02:22 AM

    @Anonymous 

    Can you please elaborate creation of "What is Sales Trend?" visual!

    It will be very helpful for me. 

     

     

    Thank you

    Message 37 of 85
    38,072 Views
    0
    Reply
    Chandrra
    Chandrra Helper I
    Helper I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-18-2020 06:31 AM
    Very nice dashboard. I do have a similar required. Is it possible to share the PBIX file with balachandra.srinevasalu@gmail.com ?
    Message 36 of 85
    41,234 Views
    0
    Reply
    lorenabarbitta
    lorenabarbitta
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-11-2020 06:06 PM

    Hola el cuadro de mando muy bueno! ¿Serías capaz de compartir el pbix o simplemente describir cómo hiciste lo rentable / no rentable como una leyenda? y como hago un grafico de tendencia

    Message 35 of 85
    40,795 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-02-2019 06:53 AM

    Hi,

    would you be willing to share this Pbix file with me? My email is drmuralimca@gmail.com 

    Message 34 of 85
    40,350 Views
    0
    Reply
    LinhDangTU
    LinhDangTU
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-09-2019 07:32 PM

    @Anonymous 

    Hello Jared.

    It's a wonderful dashboard. I really want to learn from it. Would you mind sharing the PBIX file with me to linhdanghoang@gmail.com?

    Message 33 of 85
    37,587 Views
    0
    Reply
    estapeace
    estapeace
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-09-2018 09:27 AM

    @AnonymousThis is such an awesome dashboard! Do you mind walking me through how you created this dashboard?

    Message 32 of 85
    37,218 Views
    0
    Reply
    Guidotreur
    Guidotreur
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-04-2018 01:57 AM

    Hello Jared,

     

    Would you mind sharing the Pbix file for this report with me? my email is guidotreur@yahoo.com

     

     

    Thank you 

    Message 31 of 85
    35,313 Views
    0
    Reply
    Chanleakna123
    Chanleakna123 Post Prodigy
    Post Prodigy
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-18-2018 06:31 AM

    @Anonymous, really great , i just visited your Gallary and find this is really amazing and helpful. 
    But i still have no idea how to get the Bar Chart in Between red and gray. 

    i also use Color Saturation , and i set the mimium and maximium , but the between data still can't get it right to the red color. 


    Would you please to let me know how the trick you use on this case ? 
    You're amazing 🙂 

     

    thx 

    Message 29 of 85
    34,656 Views
    0
    Reply
    Matt_Alexander
    Matt_Alexander Advocate III
    Advocate III
    In response to Chanleakna123
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-20-2018 08:39 AM

    @Chanleakna123

     

    Have you tried using the 'Diverging' Option in the Data Colors format options when you have the bar chart selected?

     

    Diverging Colors.PNG

     

    I believe that should do the trick?

     

    Matt

    Message 30 of 85
    30,377 Views
    1
    Reply
    owenitian
    owenitian Helper I
    Helper I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-23-2018 10:27 PM

     Awesome work Jared. Its surely out of box thinking...

    Could you please share the PBIX file of this scorecard to owenitian@gmail.com if you dont mind.

    Message 27 of 85
    29,806 Views
    0
    Reply
    Drew248
    Drew248
    Frequent Visitor
    In response to owenitian
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-04-2018 06:17 AM

    Hello Jared,

     

    Would you mind sharing the Pbix file for this report with me? my email is adrewmc1@gmail.com

     

     

    Thank you 

    Message 28 of 85
    29,616 Views
    0
    Reply
    EllenSu
    EllenSu
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-04-2018 11:42 AM

    Highly informative, visually appealing report!  Even more, I appreciated the writeup and the time it took to document your methodology.  Thanks for sharing!  -Ellen

    Message 26 of 85
    29,853 Views
    0
    Reply
    iDataDrew
    iDataDrew Advocate IV
    Advocate IV
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-13-2017 07:48 PM

    Aesthetically pleasing and well thought-out.  I like the way you thought outside of the box (big time!) with regard to the KPI.  This is a very well-organized and actionable report.  Thanks for sharing!

    Message 25 of 85
    28,790 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-12-2017 12:55 PM

    4/12 Update: Simplify using UNICHAR

     

    I just discovered the UNICHAR function in DAX, and used this to simplify my process! Currently we have 2 elements for each indicator, for a total of 8 elements. This method cuts the elements in half!

     

    1. Delete the tree maps and the arrow objects
    2. Delete the KPI Red/Green measures for Sales and Profit (4 total)
    3. Create 4 new measures
      • Sales Indicator Positive = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Sales YoY Growth]>0),UNICHAR(11205),"")
      • Sales Indicator Negative = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Sales YoY Growth]<0),UNICHAR(11206),"")
      • Profit Indicator Negative = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Profit YoY Growth]<0),UNICHAR(11205),"")
      • Profit Indicator Positive = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Profit YoY Growth]>0),UNICHAR(11206),"")
    4. Put each measure in a separate card.
    5. Change the Data Label colors to make the down arrows red and the up arrows grey
    6. Arrange your arrows and you're done!

    With this method, we are using the UNICHAR to display UNICODE arrow characters. Since we're doing this in DAX, we can us IF statements to hide/show the arrows based on our measures, which means we no longer need to use the tree maps as covers, cutting our number of elements in half! Less elements = easier to maintain.

     

    Enjoy!

    Message 23 of 85
    26,920 Views
    3
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices