Forum Discussion

Sirlearnalot's avatar
Sirlearnalot
New Member
7 years ago
Solved

Line Chart: Conditional Formatting

Hi, I'm trying to visualise e.g.. sales for a product during a year with campaign periods. Say the product was on campaign in Q1 and Q3 and not Q2 and Q4, I would like to show this in a line chart...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Sirlearnalot,

     

    For your scenario, you should create the four measures below to achieve your desired output.

     

    Yes_P1 = CALCULATE(SELECTEDVALUE(Table1[Sales]),FILTER('Table1','Table1'[Campaign period]="Yes"&&'Table1'[Product]=1))

Yes_P2 = CALCULATE(SELECTEDVALUE(Table1[Sales]),FILTER('Table1','Table1'[Campaign period]="Yes"&&'Table1'[Product]=2))

No_P1 = CALCULATE(SELECTEDVALUE(Table1[Sales]),FILTER('Table1','Table1'[Campaign period]="No"&&'Table1'[Product]=1))

No_P2 = CALCULATE(SELECTEDVALUE(Table1[Sales]),FILTER('Table1','Table1'[Campaign period]="No"&&'Table1'[Product]=2))

    Then you drag these measure to Values Field to create the line chart.

     

     

    In addition, you could refer to this attachment.

     

    Best Regards,

    Cherry

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi Sirlearnalot,

 

I'm not very clear about your scenario.

 

It seems that there is no direct option of conditional formatting for line chart, but we could set the color each line based on legend.

 

If you do not have the column for legend, we could create the measure to achieve it.

 

You could have a reference of this similar thread.

 

If you still need help, please share some data sample so that I can copy and test to get your desired output.

 

Best  Regards,

Cherry

  • Sirlearnalot's avatar
    Sirlearnalot
    New Member
    7 years ago

    Thank you v-piga-msft !

    To explain what I mean:

    I want to show Quarter on x, Sales on Y and Product as the legend, but with colour change if Campaign period says yes or no. 

    ProductSalesQuarterCampaign period
    170001Yes
    150002No
    165003Yes
    140004No
    2800001No
    2150002Yes
    270003No
    275004No

     

    I saw the post you referred to and it's exactly the result I want, but not sure how to apply it when it's a "4th variable", maybe I can structure the data differently?

     

    • v-piga-msft's avatar
      v-piga-msft
      Resident Rockstar
      7 years ago

      Hi Sirlearnalot,

       

      For your scenario, you should create the four measures below to achieve your desired output.

       

      Yes_P1 = CALCULATE(SELECTEDVALUE(Table1[Sales]),FILTER('Table1','Table1'[Campaign period]="Yes"&&'Table1'[Product]=1))

Yes_P2 = CALCULATE(SELECTEDVALUE(Table1[Sales]),FILTER('Table1','Table1'[Campaign period]="Yes"&&'Table1'[Product]=2))

No_P1 = CALCULATE(SELECTEDVALUE(Table1[Sales]),FILTER('Table1','Table1'[Campaign period]="No"&&'Table1'[Product]=1))

No_P2 = CALCULATE(SELECTEDVALUE(Table1[Sales]),FILTER('Table1','Table1'[Campaign period]="No"&&'Table1'[Product]=2))

      Then you drag these measure to Values Field to create the line chart.

       

       

      In addition, you could refer to this attachment.

       

      Best Regards,

      Cherry

      • Sirlearnalot's avatar
        Sirlearnalot
        New Member
        7 years ago

        Hi,

        Sorry for late replay! I actually solved it in an other way and got the result I was looking for:

         

         

         

        The dotted line is the "no campaign" and the green the campaign period during the year.

        What I did was to put the values for "campaign" and "no campaign" in different columns (even though they represent total quantity for that period) and then made a measure:

        TotalQuantity = SUMX('2018';'2018'[Quantity No campaign]+'2018'[Quantity Campaign])

        And then put TotalQuantity and Quantity Campaign as values.