cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

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.

amitchandak
Super User
Super User

@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



!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors