topic Re: Percent of total column with dynamic filter in DAX Commands and Tips

Percent of total column with dynamic filter

Anonymous — Wed, 26 May 2021 13:56:35 GMT

Hi,

I'm new to Power Pivot ")

trying to calculat the percantage of total column for several columns (years).

when i use the "ALLSELECTED" formula, i get the percentage of the Grand total and not by column.

Help would be much appreciated.

this is the formula im using:

DIVIDE([Sum of Volume (SU)],CALCULATE([Sum of Volume (SU)],ALLSELECTED(Consolidatedtable)))

Re: Percent of total column with dynamic filter

Fowmy — Wed, 26 May 2021 14:22:20 GMT

Anonymous

Can you share a screenshot or p[rovide sample data and the expected results?

Re: Percent of total column with dynamic filter

Anonymous — Wed, 26 May 2021 14:31:56 GMT

Hi,

total colums should amount to 100%.

The Pivot table is combined (appended) of two tables (2020 / 2021).

Thanks

Re: Percent of total column with dynamic filter

Fowmy — Wed, 26 May 2021 14:36:45 GMT

Anonymous

Try the following measure, you need to remove the filter on the SUB_CATEGIORY COLUMN

Vol Mix % = DIVIDE( [Sum of Volume (SU)], CALCULATE( [Sum of Volume (SU)], ALLSELECTED(Consolidatedtable[2.9.8 SUB CATEGORY_EN]) ) )

Re: Percent of total column with dynamic filter

Anonymous — Wed, 26 May 2021 15:15:11 GMT

so basically i need to add a filter reference in the CALCULATE formula that is the same as the dimension ? meaning if i change the dimension from SUB-CATEGORY to CATEGORY, the formula will not work. Is there a different way to calculate the percentage the will be dynamic for any column\row change in the pivot table?

thanks 🙂

Re: Percent of total column with dynamic filter

Fowmy — Wed, 26 May 2021 15:59:25 GMT

Anonymous

Not possible to keep it dynamic as you need to remove filter only on the rows and keep the filter on the columns.

if you apply ALLSELECTED() without parameter as an argument to CALCULATE, it will remove all the filters within the visual.

the solution is to create individual measures.

hope it clarified

Re: Percent of total column with dynamic filter

Anonymous — Wed, 26 May 2021 18:49:50 GMT

I tried creating individual measures for each year, but now i get duplicate fields,

under each year column i get both years data.

this is the DAX i used.

please HELP!

DIVIDE(SUMX(Conso,[Total Volume 2020]),CALCULATE([Total Volume 2020],ALLSELECTED()))

Re: Percent of total column with dynamic filter

Fowmy — Wed, 26 May 2021 18:55:33 GMT

Anonymous

Add an IF condition:
If ( selectedvalue(Table[Year]) = 2020,
DIVIDE(SUMX(Conso,[Total Volume 2020]),CALCULATE([Total Volume 2020],ALLSELECTED()))
)