How can I get a summary results based on the mesures

[Anonymous]

Hi,

I am struggling with the summary results and would like to get some assistance. Thank you in advance for the supports!

I have a table like the 1st screen shot and would like to get a summary like the 2nd screen shot in orange.

By the way, HIGHEST EDU is a measure that I created in order to find the highest eduction of each employee.

HIGHEST EDU = CALCULATE(MAX(table[Education Rank]), ALLEXCEPT(table, table[ID]))

Thanks!

[Anonymous]

Hi Lionel,

Thank you for your assistance and I was able to repliate what you had done.

I am wondering if there is another way not to use allexcept function?  Since I have slicers on columns besides department?

Lindsey

[Anonymous]

Hi Liu Yang,

Thanks for the solution. As what I had posted, the one that you shared was solved.

What I am stuggling right now is that the group sum and the percentage. Would you please assist?  Thank you very much for the assistance.

[v-yangliu-msft]

Hi  @Anonymous  ,

According to this result:

You can create measure：

HASONEVALUE =
var _new=SUMMARIZE('Table','Table'[DEPT],"_value",[Sum])
return IF(HASONEVALUE('Table'[DEPT]),[Sum],SUMX(_new,[_value]))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @parry2k ,

Thanks for sharing the 2 measures and they worked fine w/ the sample data (the 2nd table in the bottom).

As applying to the actual data sets, the sum results do not work (the 1st table on the top). I don't know if this is because I have a filter to the department. Do you have any idea?  I really appreciate your assistance, you and @PhilipTreacy had taught me a lot.  Really appreciate!!!!!

[Anonymous]

Hi parry2k,

Thanks for the reminding. I will do it next time.

It seems like the sum values do not look correct. The sum for DEPT AG should be 7?

Thanks!

[parry2k]

@Anonymous you should add this as a separate question so that not to bundle everything in one place.

Here are the measures

Sum = SUMX ( ALLEXCEPT ( Edu, Edu[Dept] ), [C] ) * DIVIDE ( [C], [C] )

% = DIVIDE ( [C], [Sum] )

Here are the measures though:

[Anonymous]

Hi,

Thanks everyone assistance. I was able to get what I need.

If I need to create measures of SUM (column J) and Perentage (column K), what should I do?  Really appreciate everyone help!

[parry2k]

@Anonymous good to hear it is working for you cheers!!

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favorite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Thanks! This is what I want.

[parry2k]

@PhilipTreacy you don't need to use Highest Edu in your visual, use Edu column

[Anonymous]

Hi PhilipTreacy,

This is what I had tried so far. The results do not look correct. Would you please review again?

Thank you so much for the supports!

[Anonymous]

Hi PhilipTreacy,

Thanks for your suggestions. I have tried it and got the following results.  They do not look correct.

Would you please review again? Thank you for the supports!

DEPT    Highest Edu       C

AG              4                            3

AP              4                             4

[Anonymous]

Here is the raw data

DEPT	ID	EDU	HIGHEST EDU
AP	1		04
AP	1	01	04
AP	1	03	04
AP	1	04	04
AG	2	04	04
AP	3	03	04
AP	3	04	04
AP	4	03	04
AP	4	04	04
AG	5	03	03
AG	6	03	03
AP	7	03	04
AP	7	04	04

[parry2k]

@PhilipTreacy try this measure

C = 
COUNTX ( 
    SUMMARIZE ( 
        Edu, 
        Edu[Dept], 
        Edu[EDU],
        Edu[ID], 
        "_c", CALCULATE ( MAX ( Edu[EDU] ), ALLEXCEPT ( Edu,Edu[ID] ) ) 
    ), 
    IF ( [_c] = Edu[EDU], 1 )
) 

CHECK MY LATEST BLOG POST Year-2020, Pandemic, Power BI and Beyond

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

DEPT ID EDU HIGHEST EDU AP 1 04 AP 1 01 04 AP 1 03 04 AP 1 04 04 AG 2 04 04 AP 3 03 04 AP 3 04 04 AP 4 03 04 AP 4 04 04 AG 5 03 03 AG 6 03 03 AP 7 03 04 AP 7 04 04

[Anonymous]

Let me repost my input and results.

My input source is like the table in blue (called table)  and it records everyone's education level.  --> e.g. ID = 1, she has entered  4 eduction records: null, 01, 03 & 04 and her highest education level is 04.   I would like to get a result like the table in orange.

By the way, HIGHEST EDU is a measure that I created in order to find the highest eduction of each employee.

HIGHEST EDU = CALCULATE(MAX(table[Education Rank]), ALLEXCEPT(table, table[ID]))

Thanks!

[PhilipTreacy]

Hi @Anonymous 

I'm not following the logic that produces your summary table.  Why include highest Edu 03 and 04 but not 01?   Are you looking for a count of the number of people with each level of education, in each dept?  If so, why is the count for AP,03 blank?

regards

Phil