Group Column with Conditions

[schoden]

Hi Community, 

Is there a way I can achieve as shown in the pie chart. The data is replicated in the table below( all data is present in one table ) 

The pie chart gives distinctcount of computers with users term "Full " or "Missing", with further condition , IF "Missing" , then group into range of missing as per count of users.

a) 1-2 Missing 

b)3-4 Missing 

c) > 5 Missing 

Answer Expected :  

Computer 1 is Full 

Computer 2 is in range of 1-2 Missing 

Computer 3 is in range of 3-4 Missing 

Name	User	Signal	Term
Computer 1	user 1	approved	full
	user 2	approved	full
	user 3	approved	full
Computer 2	user 4	approved	missing
	user 5	Not Approved	Missing
	user 6	Not Approved	full
computer 3	user 7	approved	missing
	user 8	approved	missing
	user 9	approved	missing

Thanks in advance 🙂

[v-luwang-msft · Accepted solution]

Hi @schoden ，

You can follow these steps：

Step 1,import data you provide:

Step 2,use the following dax to create a new table:

Table 2 = SUMMARIZE('Table','Table'[Name],"missing value",CALCULATE( count('Table'[Term]),FILTER('Table','Table'[Term]="missing")))

 

Step 3，create new column in the new table :

about =

IF ( 'Table 2'[missing value] = blank(), "full",

IF ( 'Table 2'[missing value] >=1 &&'Table 2'[missing value] <=2, "1-2 Missing",

IF ( 'Table 2'[missing value] >=3 &&'Table 2'[missing value] <=4, "3-4 Missing",

IF ( 'Table 2'[missing value] >=5 , ">5 Missing"

) ) ) )

about2 = IF ( 'Table 2'[about] ="full", "full","missing")

Step 4，try the following measure:

About4 =

VAR about3 =

CALCULATE (

COUNT('Table 2'[about]),

FILTER (

ALL ( 'Table 2' ),

'Table 2'[missing value]<>BLANK()&&'Table 2'[about]=MAX('Table 2'[about]))

)

RETURN

( about3 )

 

Step 5,create visual base on the new table , Finally you will see:

 

If my answer helps you, you can mark it as an answer, thank you!

Best Regards,

Lucien

[schoden]

@v-luwang-msft   Thank you heaps ...Solved My problem 😄 

[parry2k]

@schoden check this dynamic basket analysis and that is what you need

Check my latest blog post Compare Budgeted Scenarios vs. Actuals 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.⚡

[schoden]

Hi @parry2k , 

Thank you for the pointing to the right resource but What I am stumbling to get is 

On the x-axis /pie chart legend "Full" and 1-2 Missing , 3-4 Missing , >5 Missing together as Legend.

I can get FULL as one legend and Missing as separate with grouping of Missings.

[schoden]

Hi @parry2k  I provided a picture for clarity