Forum Discussion

Batman_powerbi's avatar
Batman_powerbi
Frequent Visitor
2 years ago
Solved

Categorise Plus or Minus %

Hi Team below is sample data of what i am calculating against So far i am able to roll up the calculations whether by function or project and obtain a % difference between estimates/Act...
Need Help
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

Batman_powerbi , Create a measure for categorizing the difference on the basis of your difference measure

Difference Category =
SWITCH(
TRUE(),
[Difference $] <= -0.3, "-30% or less",
[Difference $] <= -0.2, "-20% to -30%",
[Difference $] <= -0.1, "-10% to -20%",
[Difference $] <= 0.1, "-10% to 10%",
[Difference $] <= 0.2, "10% to 20%",
[Difference $] <= 0.3, "20% to 30%",
"30% or more"
)

 

Then one more measure for count of category

Count of Categories =
COUNTROWS(
    FILTER(
        'Closed (TABLE)',
        [Difference Category] = EARLIER([Difference Category])
    )
)
 
And then one for percentage
Percentage of Categories =
DIVIDE(
    [Count of Categories],
    COUNTROWS('Closed (TABLE)')
)
  • Batman_powerbi's avatar
    Batman_powerbi
    Frequent Visitor
    2 years ago

    bhanu_gautam 

    thankyou for the help

    when i create the 2nd measure i obtain an error

    "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

    which i am not 100% what that means
    any thoughts?

    • bhanu_gautam's avatar
      bhanu_gautam
      Icon for Super User rankSuper User
      2 years ago

      Batman_powerbi , The issue is we have used measure instead of column

       

      Create a caluclated column by going to modelling tab

      Difference Category =
      VAR diff = 'Closed (TABLE)'[Difference %]
      RETURN
      SWITCH(
      TRUE(),
      diff <= -0.3, "-30% or less",
      diff <= -0.2, "-20% to -30%",
      diff <= -0.1, "-10% to -20%",
      diff <= 0.1, "-10% to 10%",
      diff <= 0.2, "10% to 20%",
      diff <= 0.3, "20% to 30%",
      "30% or more"
      )

       

      Then create a measure 

      Count of Categories =
      COUNTROWS(
      FILTER(
      'Closed (TABLE)',
      'Closed (TABLE)'[Difference Category] = MAX('Closed (TABLE)'[Difference Category])
      )
      )

       

      and last for percentage

      DAX
      Overall % =
      VAR totalRows = COUNTROWS('Closed (TABLE)')
      VAR categoryCount = [Count of Categories]
      RETURN
      DIVIDE(categoryCount, totalRows, 0)

      • Batman_powerbi's avatar
        Batman_powerbi
        Frequent Visitor
        2 years ago

        thanks bhanu_gautam 

        unfortunately i get a duplication of the count of each time the Project is listed in the data table 



        the true nature of the output should be like below,