Grand total should change based on slicers and filters but not on interaction

[shareezsaleem]

Hi Experts,

I am looking for a DAX measure to get the percentage on an overall total.

I don't want to change the Overall Total based on clicking on graphs or table in the dashboard - the total should only change based on the filters and slicers.

DAX:

1. % On Overall Total = DIVIDE([Sum based on selection],[Overall Total])

2. Sum based on selection = SUM(PRs_POs[Net Order Value (AED)])

3. Overall Total = CALCULATE(SUM(PRs_POs[Net Order Value (AED)]),KEEPFILTERS('Calendar'[Year])) -- this is wrong, I need a correct one.

[shareezsaleem · Accepted solution]

Got it finally.

1. Sum based on selection = SUM(PRs_POs[Net Order Value (AED)])

2. 

Overall Total Based On Filters & Slicers = Calculate([Sum based on selection],ALL(),VALUES('Calendar'[year]),PRs_POs[Deletion Indicator]<>"X")

3. % On Overall = DIVIDE(PRs_POs[Sum based on selection],[Overall Total Based On Filters & Slicers])

[v-robertq-msft]

Hi, 

According to your description, I think you are talking about the “Edit interaction” function in the Power BI desktop. I suggest you to click on the card visual that displays the total value in the card chart then select “Edit interaction” on the above then set other visuals’ interaction types to “None” like this:

For more info, please refer to this official document:

https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shareezsaleem]

Not exactly.

My requirement is simple.

I have a total (Card) based on selection, which is fine.

I need to show the mgt. what is the % on overall based on selection/slicing the data.

For. e.g. If I am selecting year 2021 in slicer, the total amout is 15M, now if I select a particular vendor (on graph) or a category, the total amount changes - which is fine. Say, I have selected a category 'AMC' and the amount is 3M, I want to show 3/15 which is 20%.

Here in my formula it shows 100% except for Month graph, if I select a month on a graph, it shows the correct percentage.

Overall Total = Divide(PRs_POs[Sum based on selection],CALCULATE(SUM(PRs_POs[Net Order Value (AED)]),ALLEXCEPT('Calendar','Calendar'[Year])))

[shareezsaleem]

Any luck to get a solution?

[amitchandak]

@shareezsaleem , Try like, if that can help

Overall Total = CALCULATE(SUM(PRs_POs[Net Order Value (AED)]),allselected(PRs_POs) )

[shareezsaleem]

No, its not working, it slice the figure whenever I click on a bar or anything on the chart.

I need to show this value as the total.

When I used the below measure, it doesn't slice the amount on month selection but for other like vendor, category, plant name, LTPA it's slices the figure. Can I please have a solution?

CALCULATE(SUM(PRs_POs[Net Order Value (AED)]),ALLEXCEPT('Calendar','Calendar'[Year]))