topic How to get % of total for filtered values in DAX Commands and Tips

How to get % of total for filtered values

PowerBIReece — Mon, 04 Jul 2022 14:31:25 GMT

Hi Guys,

I am struggling to find a way to get a % of grand total for the various years for the chart below:

Essentially, I want the year totals to work out a % of the grand total for that year. However, when I use the quick measure it is giving me the the percentage of the overall grand total for all the years summed.

How do get the % total per years only without having to filter one year at a time?

Regards,

Reece

Re: How to get % of total for filtered values

PowerBIReece — Mon, 04 Jul 2022 14:34:05 GMT

It's giving me this:

But I want to see these figures on the combined chart:

Re: How to get % of total for filtered values

ribisht17 — Tue, 05 Jul 2022 17:19:46 GMT

PowerBIReece

Guess you are making use of ALL, please turn that to ALLEXCEPT

Total Sales = CALCULATE(sum(orders_[Sales]),ALL('Calendar Table'[Year]))

Total Sales = CALCULATE(sum(orders_[Sales]),ALLEXCEPT(orders_,'Calendar Table'[Year]))

Regards,

Ritesh

Regards,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users

Re: How to get % of total for filtered values

ribisht17 — Tue, 05 Jul 2022 16:40:58 GMT

PowerBIReece

Kindly close the thread if the answer was useful or else let us know the challenge.

It will help the community to get to the answer directly

Regards,

Ritesh

Re: How to get % of total for filtered values

PowerBIReece — Wed, 06 Jul 2022 10:26:25 GMT

Hi Ritesh,

I have not yet managed to resolve this.

First off, I’d like to apologize for not being able to post my PBIX file because this confidential data I am working with.

I am currently working with survey data, the questions give the respondent a few options in terms of easy, difficult etc and this is what I’m trying to plot below:

The categories are split across the different years.

The issue I’m having is that when I select to reflect values as a % of grand total it gives me the following output:

However, these figures are incorrect, because I am looking for a way to do this across the year, not the entire sample.

So for example this is the same chart filtered by only 2022:

These are the % figures I need to see on the chart above.

Re: How to get % of total for filtered values

PowerBIReece — Wed, 06 Jul 2022 10:28:07 GMT

Apologies ribisht17

Re: How to get % of total for filtered values

Anonymous — Thu, 07 Jul 2022 05:45:09 GMT

Hi PowerBIReece ,

I have created a simple sample, please refer to my pbix file to see it helps you.

Create a measure.

Measure = VAR _SUBJECT = SELECTEDVALUE ( 'Table'[SUBJECT] ) VAR _YEAR = SELECTEDVALUE ( 'Table'[YEAR] ) VAR _SUMALL = CALCULATE ( SUM ( 'Table'[VALUE] ), FILTER ( ALL ( 'Table' ), 'Table'[SUBJECT] = _SUBJECT && 'Table'[YEAR] = _YEAR ) ) VAR _SUMYEAR = CALCULATE ( SUM ( 'Table'[VALUE] ), FILTER ( ALL ( 'Table' ), 'Table'[YEAR] = _YEAR ) ) RETURN _SUMALL / _SUMYEAR

If I have misunderstood your meaning, please provide a simple sample and desired output like I have provided.

How to Get Your Question Answered Quickly

Best Regards

Community Support Team _ Polly

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