cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted
Frequent Visitor

Re: rollup columns within one table

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors