topic Re: DAX measures: Summarize with allexcept in DAX Commands and Tips

DAX measures: Summarize with allexcept

Guille2711410 — Fri, 27 Dec 2019 16:31:08 GMT

Hi everyone

I have this measure and it works well for me

MaxRecNroTrn = SUMX(SUMMARIZE(COMTO20,COMTO20[FECVAL],COMTO20[CTAUNICA]),CALCULATE(MAX(COMTO20[RECNROTRN])

and I want this calculation to change only for the CTAUNICA column,
I used ALLEXCEPT, but it didn't work.

With the above mentioned, I want to divide this result by 11 and this new result divide it by the TOTAL column of another related table called SUNEDU.
I tried RELATED, but it doesn't work for me.
I will be very grateful with your help
Thank you
In red the related columns (CGH0NOM <-> UNIVERSIDAD)

Table COMTO20

FECHAVAL	CTAUNICA	CGH0NOM	RECNROTRN
201810	384337	UNIVERSIDAD DE LIMA	6633
201810	384337	UNIVERSIDAD DE LIMA	4359
201810	384337	UNIVERSIDAD DE LIMA	3335
201811	384337	UNIVERSIDAD DE LIMA	496
201811	384337	UNIVERSIDAD DE LIMA	3174
201811	384337	UNIVERSIDAD DE LIMA	2549
201810	547671	UNIVERSIDAD DE PIURA	207
201810	547671	UNIVERSIDAD DE PIURA	180
201810	547671	UNIVERSIDAD DE PIURA	2675
201811	547671	UNIVERSIDAD DE PIURA	3856
201811	547671	UNIVERSIDAD DE PIURA	264

Table SUNEDU

UNIVERSIDAD	GESTION	TOTAL	HOMBRES	MUJERES
UNIVERSIDAD DE LIMA	PRIVADA	19410	10504	8906
UNIVERSIDAD DE PIURA	PRIVADA	8394	4251	4143

Re: DAX measures: Summarize with allexcept

az38 — Fri, 27 Dec 2019 20:10:51 GMT

hi Guille2711410

its not obviously what you need as a result. please, give an example desired output

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: DAX measures: Summarize with allexcept

Guille2711410 — Mon, 30 Dec 2019 17:15:20 GMT

Thanks az38

The measure es:

MaxRecNroTrn = SUMX(SUMMARIZE(COMTO20,COMTO20[FECVAL],COMTO20[CGH0NOM]),CALCULATE(MAX(COMTO20[RECNROTRN])

With this measure I get this, but it should not be altered under any filter, only that of GH0NOM

FECHAVAL	CTAUNICA	CGH0NOM	RECNROTRN
201810	384337	UNIVERSIDAD DE LIMA	6633
201811	384337	UNIVERSIDAD DE LIMA	3174
			9807
201810	547671	UNIVERSIDAD DE PIURA	2675
201811	547671	UNIVERSIDAD DE PIURA	3856
			6531

For Example: for GH0NOM = UNIVERSITY OF LIMA
the total of RECNROTRN = 9807, I must divide it by 11 = 891.54
Then 891.54 divide it with the TOTAL of the SUNEDU table for UNIVERSITY = UNIVERSITY OF LIMA
891.53 / 19410 = 0.046 = 4.6%

Thank you

Re: DAX measures: Summarize with allexcept

Guille2711410 — Thu, 02 Jan 2020 16:53:17 GMT

Hi az38
Please I need help with this problem.
I want to spread the use of PBI in the company
Thank you

Re: DAX measures: Summarize with allexcept

Anonymous — Sun, 05 Jan 2020 22:31:07 GMT

Hi Guille2711410

I don't know which parameters you used in the ALLEXEPT function:

CS2 = CALCULATE(Divide(Divide([MaxRecNroTrn],11), sum(SUNEDU[TOTAL])),

ALLEXCEPT(COMTO20, COMTO20[CGH0NOM]),

filter(all(COMTO20[CGH0NOM]), COMTO20[CGH0NOM] in VALUES(COMTO20[CGH0NOM])

) )

But I think this measure gives you your required calculation. You can leave the "filter(all(COMTO20[CGH0NOM]), COMTO20[CGH0NOM] in VALUES(COMTO20[CGH0NOM])" out, then the total % will not change either.
The Filter(all..... line is added to make the visual respond to the university selection for the grand total lines.

Hope this helps.

Re: DAX measures: Summarize with allexcept

Guille2711410 — Mon, 06 Jan 2020 20:59:29 GMT

Thanks JustJan, but it doesn't work
I tried your solution with both ways but it gives me an error
Apparently what I ask is very simple
Thank you

Re: DAX measures: Summarize with allexcept

Anonymous — Mon, 06 Jan 2020 21:05:29 GMT

Hi Guille2711410 ,

What is the error message you get?

What is the relation between the two table?

Or can you share a pbix?

Re: DAX measures: Summarize with allexcept

Guille2711410 — Mon, 06 Jan 2020 21:33:21 GMT

This is the error message

You cannot determine a single value for the RECNROTRN column in the COMTO20 table. This can happen when a measurement formula refers to a column that has many values without specifying an aggregation such as MIN, MAX, COUNT or SUM to get a result. only.

The relationship between the two tables is COMTO20 (CGH0NOM) and in SUNEDU (UNIVERSIDAD).

I cannot share the pbix, because in the company for security reasons it prevents me from saving in the cloud.

Thank you

Re: DAX measures: Summarize with allexcept

Anonymous — Mon, 06 Jan 2020 22:18:35 GMT

Hi Guille2711410

I have a small pbix file, based on the information you provided in the message.

https://sharefiles.app/download/e0a7be525b054c5c73539bfaed3811b25519870c

Maybe you can have a look why it is working for me. Maybe the test data is a bit too simple / not precisely representing the actual situation.

Let me know how you are doing.

Re: DAX measures: Summarize with allexcept

Guille2711410 — Tue, 07 Jan 2020 15:42:16 GMT

Hi,

You can send your pbix file to my personal emails
I can't open it from the company where I work
Thank you

Email: guillermo.ruiz@scotiabank.com.pe or guille2711410@hotmail.com

Re: DAX measures: Summarize with allexcept

Guille2711410 — Thu, 09 Jan 2020 17:34:01 GMT

Hi Jan

Excuse me for delaying in answering you, but we are with the accounting closures that temporarily take me away from this query.
I tried to open the file you sent me but could not have a past version of the PBI installed here in the company.
I will try to open it from home today and I tell you.

regards