Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Measure filter for rate trend analysis

https://drive.google.com/file/d/1cxRnqRJVf6ZaOl7I9TbUMyMmh962rvwS/view?usp=sharing Hi,

 

First time post and long time user of Power BI and i'm currently stuck with the following scenario.

I am trying to create a rate analysis trend with conditional formatting and filter only based on Increased/decrease/no change.

Example below is within Excel of how it should look and attached power bi dummy data.

 

Dataset is the rate for different employees for different period.

MonthEmployeeRate
Jul-21A500
Aug-21A600
Sep-21A600
Oct-21A600
Nov-21A600
Dec-21A600
Jul-21B900
Aug-21B850
Sep-21B850
Oct-21B850
Nov-21B850
Dec-21B850
Jul-21C700
Aug-21C700
Sep-21C700
Oct-21C700
Nov-21C700
Dec-21C700
Jul-21D800
Aug-21D700
Sep-21D750
Oct-21D750
Nov-21D750
Dec-21D750
Jul-21E600
Aug-21E650
Sep-21E670
Oct-21E670
Nov-21E670
Dec-21E670

 

What i'm trying to achieve is being able to filter the list of employee that has had an increase or decreased within the period.

 

 

I've tried to use a disconnected table as a filter, however it will only show me the employee with the month that has an increase/decrease as example below:

 

 

 

3Measures to Show =
IF(
    HASONEVALUE(EmployeeDetail[EmployeeName]),
        IF(HASONEVALUE(RateKPI[RateKPI]), // disconnected table
            SWITCH(
          VALUES(RateKPI[RateKPI]),
          "Increased",[3Increased],
          "Decreased",[3Not Increased],
"Same",[3Same],
                ),
            [3Current COR]
        )
)
 
I'm truly stuck at the moment and hope somebody can help with similiar situation.
 
Thank you,
Ken
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , If you have a date; you can create a MOM measure and use that in conditional formatting. else create a date using the month

    Date = "01-" &[Month year]  /// change data type to date

    Use date table

    Create a month in date tanle and use that in visual

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

    MOM = [MTD Sales] - [Last MTD Sales]

     

    Create color  =

    if([MOM] <0 , "Red", "Green" )

     

    How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    Use this in conditional formatting using field value option

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for replying so quickly amitchandak 

       

      I've tried the formula but i'm not looking for a difference in current month sales and previous month sales.

      I'd like to keep the rates there and if a slicer filter is selected of "Decreased or "Increased".

       

      Picture below is if the slicer is unfiltered, decreased (red) and increased (green).

      Keeping the same amounts within the selected filter with the conditional formatting.

       

       

      I hope this makes sense.

       

      Thanks,

      Ken