Forum Discussion

PMEH's avatar
PMEH
Frequent Visitor
2 years ago
Solved

Power bi

Hi I have a measure that calcluates the %

Add Pen% = DIVIDE(PA00109_HOMES_PASSED_DATAHEN_AGG[TotalWarmAddresses_Agg],PA00109_HOMES_PASSED_DATAHEN_AGG[TotalHP_Agg],0)

Here is the sample of data:

Now I have to bucket these percentages as following and I should be able to use it as a filter.

"0%-10%", 
"11%-20%",
"21%-30%"
"31%-40%"
"41%-50%"
"51%+"
 
Any help or direction will be appreciated.
Thanks,
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi PMEH ,

    First of all, many thanks to  for your very quick and effective replies.

    Based on my testing, please try the following methods:

    1.Create the simple table.

    2.Create the new measure to calculate the percentage.

     

    Add pen = 
VAR hp_ = SUMX('Table', 'Table'[Total hp])
VAR hpa = SUMX('Table', 'Table'[Total hp agg])
RETURN
DIVIDE(hp_, hpa)

     

    3.Create the measure to filter the percentage.

     

    Percentage = 
SWITCH(
        TRUE(),
        [Add pen] <= 0.1, "0%-10%",
        [Add pen] <= 0.2, "11%-20%",
        [Add pen] <= 0.3, "21%-30%",
        [Add pen] <= 0.4, "31%-40%",
        [Add pen] <= 0.5, "41%-50%",
        "51%+"
    )

     

    4.Drag the measure into the table visual. The result is shown below.

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi PMEH ,

    First of all, many thanks to  for your very quick and effective replies.

    Based on my testing, please try the following methods:

    1.Create the simple table.

    2.Create the new measure to calculate the percentage.

     

    Add pen = 
VAR hp_ = SUMX('Table', 'Table'[Total hp])
VAR hpa = SUMX('Table', 'Table'[Total hp agg])
RETURN
DIVIDE(hp_, hpa)

     

    3.Create the measure to filter the percentage.

     

    Percentage = 
SWITCH(
        TRUE(),
        [Add pen] <= 0.1, "0%-10%",
        [Add pen] <= 0.2, "11%-20%",
        [Add pen] <= 0.3, "21%-30%",
        [Add pen] <= 0.4, "31%-40%",
        [Add pen] <= 0.5, "41%-50%",
        "51%+"
    )

     

    4.Drag the measure into the table visual. The result is shown below.

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.