Measure percentage of one variable based on two other columns without loosing filters

[Anonymous]

so the data I have is similar to this;

category	volume	date	type
a	1	jan	group1
a	4	jan	group1
b	12	jan	group2
c	2	jan	group1
b	7	feb	group1
a	9	feb	group2
c	10	feb	group1
c	4	feb	group2
b	8	mar	group1
c	3	mar	group1
a	1	mar	group1
a	11	mar	group2

and I have a page level filter of type; group1. I would like a graph that represents a percentage of the total per month but without loosing the filter. something like this;

	A	B	C
JAN	71%	0%	29%
FEB	0%	41%	59%
MAR	8%	67%	25%

the best I have is;

measure=

divide(

Calculate(sum(table[volume]),allexcept(table,table[type],table[date])),

calculate(sum(table[volume]),allexcept(table,table[date])

)

the issue with this is that the allexcept function doesnt take into account filters.

can anyone help with a solution?

thanks!

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create a measure like this:

Measure = 
VAR x =
    CALCULATE (
        SUM ( 'Table'[volume] ),
        FILTER ( 'Table', 'Table'[Cal].[Month] IN DISTINCT ( 'Table'[Cal].[Month] ) )
    )
VAR xx =
    IF ( x = BLANK (), 0, x )
VAR y =
    CALCULATE (
        SUM ( 'Table'[volume] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Cal].[Month] IN DISTINCT ( 'Table'[Cal].[Month] )
        )
    )
RETURN
    DIVIDE ( xx, y )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB]

Hi @Anonymous 

Try

Measure = DIVIDE(CALCULATE(SUM(Table1[volume])), CALCULATE(SUM(Table1[volume]), ALL(Table1[category])))

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers