Forum Discussion

Hikmet_JCI's avatar
Hikmet_JCI
Helper I
1 year ago
Solved

Need help matrix table

Hello everyone Need help? I have a table with these columns: Name, FY, YP, Target, Actual, Lead and two calculated columns: Quota (%), (Actual/Target) I need a new column: WENN Lead <=0, "S" ...
Need Help
  • v-priyankata's avatar
    v-priyankata
    1 year ago

    Hi  Hikmet_JCI 

    Thank you for using Microsoft Community Forum.

     

    You can absolutely use your logic both in a calculated column for a table and in a matrix visual in Power BI. Here’s how you can approach both:

     

    1. Calculated Column in a Table, To display this classification logic in a table visual, create a calculated column in Power BI using this DAX:

     

    Category = IF ('Table'[Lead] <= 0 && 'Table'[Quota (%)] < 0.80,
        "SS",IF ('Table'[Lead] = 0,"S",IF ('Table'[Lead] < 4,"L",IF ('Table'[Lead] = 4,"M","H"))))

     

    • Go to your table in Data view. Click New Column and paste the above code. this will create a new column (e.g., Category) with values like SS, S, L, M, or H depending on your conditions.

     

    2. Use in a Matrix Visual, Now that you've created the column:

    • Add a Matrix visual to your report.
    • Use the new Category column in Rows or Columns, depending on how you want to group.
    • Add your numeric values (e.g., Lead, Quota (%), etc.) in Values.
    • You can also use the same logic directly in a measure if needed for calculations.

     

    If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
    Thank you.

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

Hi Hikmet_JCI ,
First create a new calculated column by the bellow DAX to create the conditions:

Column = 
    IF(
        'Table'[Lead] <= 0 && 'Table'[Quota (%)] = 0,
        "SS",
        IF(
            'Table'[Lead] <= 0, 
            "S",
            "H"
        )
    )

 now create a new measure to count the YP by this DAX:

Total YP = COUNT('Table'[YP])

After that, add a new Matrix, and place the YP in rows, the calculated column in column field, and the measure Total YP in values as bellow:

 

Based on gived data, the output will look like this:

 

Let me know if this satisfies your need, and feel free to ask whe something goes wrong.

If this help you, please, consider to accept this reply as solution and give a Kudo.

 

Thank you

  • Hikmet_JCI's avatar
    Hikmet_JCI
    Helper I
    1 year ago

    Hi.

    Thanks.

    How do I get it to display correctly when filtering in YP?

    As in the image:
    Filter 1-3
    Leads are 6 in three periods, 3 periods / 6 = 2, so only L should appear in Column and not multiple times...

    Have Column new
    Column =
    IF(
    'Table'[Lead] <= 0 && 'Table'[Quota (%)] < 0.80,
    "SS",
    IF(
    'Table'[Lead] <= 0,
    "S",
    IF(
    'Table'[Lead] <= 4,
    "L",
    IF(
    'Table'[Lead] = 4,
    "S",
    "H"
    )
    )
    ))



    I need it for a table and matrix !!

    • Bibiano_Geraldo's avatar
      Bibiano_Geraldo
      Super User
      1 year ago

      just to understand, 

      becasue "Leads are 6 in three periods, 3 periods / 6 = 2" should only show L because L's YP = 2, right?

      For example, if that calculations = 1 it was supposed to show SS? is this?

      • Hikmet_JCI's avatar
        Hikmet_JCI
        Helper I
        1 year ago

        Hi..

        Here it is again, correctly.

        Column =
        IF(
        'Table'[Lead] <= 0 && 'Table'[Quota (%)] < 0.80,
        "SS",
        IF( 'Table'[Lead] = 0,
        "S",
        IF( 'Table'[Lead] < 4,
        "L",
        IF( 'Table'[Lead] = 4,
        "M",
        "H"
        ) )))

        I need this in a table and in a matrix....