Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.