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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

rollup columns within one table

Hello,

I am attempting to recreate a a pretty simple dahboard that i built in excel, but am having difficulties in setting columns to roll up to different levels within the same table.  My dash is set up as such:  A sales person chooses their name from a drop down at the top.  They can then look at their sales results by product and compare them to the results of the total state and the total country.

here is an example:

dropsown: sales person

columns: sales person's results || total state results || total country results

rows: products

the total country column will remain static by product, but the state column will change states depending on where the sales person resides. 

To do this, i use sumifs function referencing hidden columns and set up like this: =sumifs(sales, state=filtered state, sales person=filtered sales person, product=product intable)

I have a data sheet on another page that compares the values in the hidden columns to the data sheet.

My problem is that I am not able to create a rollup column that is not affected by the visual level filters.  For example, If I filter to a sales person, my state column will reflect sales only from that sales person and not the whole state. 

I tried setting up a seperate summarized table and connect them, but that doesnt interact with my brand filters.

This is so simple in excel, but I'm stuck in PBI!  Thank you for helping!

 

1 REPLY 1
Anonymous
Not applicable

Hello,

 

Can you upload here a PBI file sample so I can understand better what you trying to achive?

 

I dont know if this helps, but there is also the "Edit Interactions" button where you can manage which filters should/should not affect your visuals.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors