Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Pivot with Empty Column

Dear:

I have a query, I have made some crosses with a table that I have, which is called Key Players, which are some of the products that I want to evaluate, it is not the total of the universe, but obviously since I do not have some of the competitors in that table, A blank box appears. How could I make sure that instead of the White in the first row showing me the word "OTHERS", the measure I am using for the calculation is:

VentaUSMk = CALCULATE([VentaMk],'Market Defn'[Medida]="US")

Thanks in advance for the prompt response

jminanoc_0-1632839990372.png

  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Syndicate_Admin 

     

    You need to have "Others" in the KEY_PRODUCT column if you want it to be displayed. You could add a new column to data table to group products except for the five listed products into a group "Others", then use this new column as row field in the matrix visual. 

     

    Group column =
IF (
    'table'[KEY_PRODUCT]
        IN { "ProductA", "ProductB", "ProductC", "ProductD", "ProductE" },
    'table'[KEY_PRODUCT],
    "Others"
)

     

     

    Additionally, if you hope the "Others" group to be dynamic as you would change key products with a slicer or filter, you could refer to the following blog. It has a great solution there. 

    Dynamic Grouping in Power BI using DAX – Some Random Thoughts (sqljason.com)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Syndicate_Admin , You need to have product or competitors  in the table(dimension table, on on row)  with others, if you want to display other in place of blank

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Syndicate_Admin 

     

    You need to have "Others" in the KEY_PRODUCT column if you want it to be displayed. You could add a new column to data table to group products except for the five listed products into a group "Others", then use this new column as row field in the matrix visual. 

     

    Group column =
IF (
    'table'[KEY_PRODUCT]
        IN { "ProductA", "ProductB", "ProductC", "ProductD", "ProductE" },
    'table'[KEY_PRODUCT],
    "Others"
)

     

     

    Additionally, if you hope the "Others" group to be dynamic as you would change key products with a slicer or filter, you could refer to the following blog. It has a great solution there. 

    Dynamic Grouping in Power BI using DAX – Some Random Thoughts (sqljason.com)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.