Percent of Column Total and drilling

[ellevy12]

Hi

i had a similar question answered here  - Question , but now i need to make a drill also on the columns , and the number just got all wrong.

my data set is (simpiflied for the question):

Priority	Number of registrants	SCHOOL	FACULTY	SECTION	DATE
1st	10	X	1	A	1.1.2018
2nd	20	X	1	A	1.1.2018
1st	32	X	1	A	5.1.2018
2nd	40	X	1	A	5.1.2018
1st	40	Y	2	B	1.1.2018
2nd	80	Y	2	B	1.1.2018
1st	60	Y	2	B	5.1.2018
2nd	20	Y	2	B	5.1.2018

i want a matrix visual of the following :

	Date
	5.1.2018	1.1.2018
	Priority	Priority
Section	1st	2nd	Total	1st	2nd	Total
A	220%	100%	140%	0%	0%	0%
B	50%	-75%	-33%	0%	0%	0%

using the code:

% registrants = 

VAR previousDate =

    CALCULATE (

        MAX ( MyTable[Date] ),

        FILTER ( ALLSELECTED ( MyTable), MyTable[Number of registrants] > 0 &&  MyTable[Date]< MAX ( MyTable[Date] ) )

    )

VAR previousREG =

    CALCULATE (

        SUM ( MyTable[Number of registrants] ),

       FILTER ( ALLEXCEPT(MyTable,MyTable[SECTION]) ,mytable[prioirty] ,  MyTable[Date]= previousDate )

    )

RETURN

           DIVIDE( SUM ( MyTable[Number of registrants] ) -previousReg,previousReg,0)

However it seems that the drill on the column size is not affected although i wrote the priority column on the FILTER ALLEXCEPT dax statement for some reason...

instead of calc of ( SECTION A ; Prioirty 1st)  :(32-10)/10  = 220% it actually calc  (32-30)/30 . it seems that the (FILTER(ALLEXCEPT) dax statement is missing something on the column side, and instead of sum only the part of the prioirty , it sums the total (1st + 2nd) . how can i enforce powerbi to sum only the priority and not the total of priority ?

Help is appreciated

Elad

[v-lili6-msft · Accepted solution]

hi, @ellevy12

After my test, you need to improve your formula as below:

% registrants = 

VAR previousDate =

    CALCULATE (

        MAX ( MyTable[Date] ),

        FILTER ( ALLSELECTED ( MyTable), MyTable[Number of registrants] > 0 &&  MyTable[Date]< MAX ( MyTable[Date] ) )

    )

VAR previousREG =

    CALCULATE (

        SUM ( MyTable[Number of registrants] ),

       FILTER ( ALLEXCEPT(MyTable,MyTable[SECTION],MyTable[Priority]),MyTable[DATE]= previousDate )

    )

RETURN

         var result=  DIVIDE( SUM ( MyTable[Number of registrants] ) -previousReg,previousReg,0) return
result

CALCULATE (

SUM ( MyTable[Number of registrants] ),

FILTER ( ALLEXCEPT(MyTable,MyTable[SECTION],MyTable[Priority]) , MyTable[Date]= previousDate )

)

you can also add other conditions in it.

Because you had added new conditional MyTable[Priority] into matrix visual, so you need to add this conditional into your

formula too.

Result:

and for your reply "how can i not display the min date ? since it's all 0 anyway due to not have diffrence of change "

I have test a lot for it, I'm afraid it couldn't achieve for now. The simplest method is filter it by slicer.

Sorry for the late reply for it.

Best Regards,

Lin