Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Conditional Format on Column Chart - Multiple locations

Hoping to get some insight on solving this problem.

 

1. See attached layout of multiple column charts on a page.  I have two such pages : Average Time and Sales.

The value on the chart is obtained from a calculated measure. Also take note the the slicer to display "Internal" and "External" value.  

 2.  This is a summary of what I am trying to achive.

 

  • The target for each country will be maintained in a shaporeint table. The layout of this table is not rigid. Please feel free to suggest a suitable layout. For explanation, an example is shown herewith.

     

    I am hoping to find a solution where the color of the bar changes to either red or green based on whether the target was hot or miss.

Thanks in advance and much appreciated.

 

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Anonymous ,

     

    Create a table using below dax expression:

    Table 2 = ADDCOLUMNS(CROSSJOIN(VALUES('Table'[Country]),UNION(ROW("Target Type","Average Time"),ROW("Target Type","Sales"))),"Internal",
  SWITCH(TRUE(),
  [Target Type]="Average Time",CALCULATE(AVERAGE('Table'[Time(days)]),FILTER('Table','Table'[Group]="Internal"&&'Table'[Country]=EARLIER('Table'[Country]))),
  [Target Type]="Sales",CALCULATE(SUM('Table'[Sales ('000)]),FILTER('Table','Table'[Group]="Internal"&&'Table'[Country]=EARLIER('Table'[Country])))),
  "External",
    SWITCH(TRUE(),
  [Target Type]="Average Time",CALCULATE(AVERAGE('Table'[Time(days)]),FILTER('Table','Table'[Group]="External"&&'Table'[Country]=EARLIER('Table'[Country]))),
  [Target Type]="Sales",CALCULATE(SUM('Table'[Sales ('000)]),FILTER('Table','Table'[Group]="External"&&'Table'[Country]=EARLIER('Table'[Country])))))

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

  • Anonymous's avatar
    Anonymous
    5 years ago

    Many thanks Kelly. Your solution partially answered my question and I could tweak it a little to suit my needs.

     

4 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Could you pls provide a monthly data of each country for test?

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, appreciate your effort.

       

      DummyData 

      Trust this will help.

       

      Thanks in advance.

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi Anonymous ,

         

        Create a table using below dax expression:

        Table 2 = ADDCOLUMNS(CROSSJOIN(VALUES('Table'[Country]),UNION(ROW("Target Type","Average Time"),ROW("Target Type","Sales"))),"Internal",
  SWITCH(TRUE(),
  [Target Type]="Average Time",CALCULATE(AVERAGE('Table'[Time(days)]),FILTER('Table','Table'[Group]="Internal"&&'Table'[Country]=EARLIER('Table'[Country]))),
  [Target Type]="Sales",CALCULATE(SUM('Table'[Sales ('000)]),FILTER('Table','Table'[Group]="Internal"&&'Table'[Country]=EARLIER('Table'[Country])))),
  "External",
    SWITCH(TRUE(),
  [Target Type]="Average Time",CALCULATE(AVERAGE('Table'[Time(days)]),FILTER('Table','Table'[Group]="External"&&'Table'[Country]=EARLIER('Table'[Country]))),
  [Target Type]="Sales",CALCULATE(SUM('Table'[Sales ('000)]),FILTER('Table','Table'[Group]="External"&&'Table'[Country]=EARLIER('Table'[Country])))))

        And you will see:

        For the related .pbix file,pls see attached.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!