Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Bar Chart with Variable Target Line

Hi All,

 

I have a hopefulyl a simple problem.... 

 

I have 50 Locatons, these are broken into 4 Regions, and 6 Business Divisions. 

I have the actual performance calcualted and working for each site, when filtered this gives the cauclated average for region and bsuiness division. 

 

Problem is with the target line as there are multiple targets per location. I cannot get the target line to match the actual target when filtering. See attached image at bottom, the year end caluclated target is 80 not 75. 

 

If I select the target at location level it is correctly displayed. 

If I select at Region or Business I only have the average which is skewed and not correct. 

 

Thanks

Need Help

4 Replies

  • Bhuvaraghan's avatar
    Bhuvaraghan
    Frequent Visitor
    5 years ago

    Anonymous 

    I assume you are having a seperate column or a table for targets for each region, division and location. If you are going to give the user an option to single select each of these at a time, you can create a seperate dax for targets refrencing each target:

     

    Target = IF(SELECTEDVALUE('Base Data'[Location])<>"",AVERAGE('Base Data'[Location Target]),
    IF(SELECTEDVALUE('Base Data'[Business Division])<>"",AVERAGE('Base Data'[Division Target]),
    IF(SELECTEDVALUE('Base Data'[Region])<>"",AVERAGE('Base Data'[Region Target]),5000)))
     
    Selected value checks if a single value is selected from a column, in case all values are selected it returns a blank. Using that locig I have built a nested if to give the output. When there are no selections I have given a common target.Location is selectedRegion is selectedDivision is selected
     
    In case you find this helpful, give a thumps up and mark as solved.
     
    Thanks,
    Bhuvaraghan
    Vivid Visualization
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Anonymous - Sorry, having trouble following, can you post sample data as text and expected output?


    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

  • ravi14's avatar
    ravi14
    Frequent Visitor
    4 years ago

    Hi Audience,

     

    I am trying to achieve Bar chart with fixed Target Line 

     

    Target Lin Value: 14.5%

    Bar Chart Values: Variable keeps on changing eg:  4% 8% 16% 20% 32% 40 % max is 100 %

     

    Need to display 

    If Below 14.5 % Green Bar 

    Above 14.6 % Red bar as it has reached above the target values i have Data Colors and Rules and set for that particular column it does not work need your suggestions and inputs.

     

    Rule: If value >= 0 percent and <= 14.5 Percent then Green

             If value >==14.6 percent and <= 100 percent  then Red

     

    It doe not work color does not change with the Rules applied. Need you help extremly stuck for a week now.