Percent of total column with dynamic filter

[Anonymous]

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)))

[Fowmy]

@Anonymous 

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

[Anonymous]

Hi,

total colums should amount to 100%.

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

Thanks

[Fowmy · Accepted solution]

@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])
    )
)

[Anonymous]

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 🙂

[Fowmy]

@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 

[Anonymous]

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()))

[Fowmy]

@Anonymous 

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