Forum Discussion
Mutually Exclusive Member count
Hi All,
Issues calculating mutually exclusive member counts.
i have data like this:
| RED | WHITE | YELLOW | BLUE | P1 | P2 | P3 | P4 |
| Y | Y | Y | N | 1 | 1 | 1 | |
| Y | N | Y | N | 1 | 1 | ||
| N | N | Y | N | 1 | |||
| Y | N | Y | Y | 1 | 1 | 1 | |
| N | Y | N | Y | 1 | 1 | ||
| Y | Y | N | N | 1 | 1 | ||
| Y | N | N | N | 1 |
Note: P1,P2,P3&P4 where ever Y is there i consider it 1
following steps:
1.Imported data
2.Selected unpovited columns RED,WHITE,YELLOW& BLUE(here my data is duplicated)
3.Again i unpovied colums P1,P2,P3&P4(Here number of records increased)
4. my data like this
| A | B | C | D |
| RED | Y | P1 | 1 |
| RED | N | P2 | 1 |
| YELLOW | Y | P4 | 1 |
| WHITE | Y | P3 | 1 |
| RED | N | P2 | 1 |
5.Now i created mapping file
| Slicer_name | value |
| RED | P1 |
| WHITE | P2 |
| YELLOW | P3 |
| BLUE | P4 |
6.i marged 4&5 tables based on VALUE and C
7.i got output like this
| A | B | C | D | Slicer_name |
| RED | Y | P1 | 1 | RED |
| RED | N | P2 | 1 | RED |
| YELLOW | Y | P4 | 1 | YELLOW |
| WHITE | Y | P3 | 1 | WHITE |
| RED | N | P2 | 1 | RED |
8.i created four table P1,P2,P3&P4 same as below
| Column |
| RED |
| YELLOW |
| WHITE |
| BLUE |
9.i) created Four Measures
example:
p1=var x=selectedvalue(p1(column))
retrun
if(x=selectedvalue(A),calculate(count(b),filter(sheet1(b)="Y"),sheet1(slicer_name)=x))
ii)
p2=var x=selectedvalue(p1(column))
var y=selectedvalue(p2(clumns))
retrun
if(x=selectedvalue(A),calculate(count(b),filter(sheet1(b)=y),sheet1(value)="N",sheet1(slicer_name)=x))
"Till here i got correct values"
10. Output:
| Columns | FInal |
| RED | 5 |
| YELLOW | 6 |
this output dynamically changing the data.
11. Now i'm not able to do P3 & P4
(Note: if user select any priority next priority first priority members are mutually excluding)
Example: if user select P1=red(Y=3)
P2=white(Y=1)
P3=yellow(Y=1)
user select dynamically change data.
My logic is working P1&P2 but P3& P4 im getting worng Values.
Please Helping me this,
Any suggeste Apprisated,
Best Records,
Sai Kiran.
6 Replies
Anonymous
Wher is your source data and how does look? And, what kind of output are you expecting?
Please share some sample data,If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and ExcelSample Data:
id blue red black white 1 Y Y Y Y 2 N Y N N 3 N N Y N 4 N N N Y 5 Y Y N Y 6 Y N Y Y In visulation:
i need to sow nour slicers
P1,P2,P3&P4
P1 RED WHITE BLUE BLACK simillarly P2,P3 & P4
i need Output:
If user select dynamically P1,P2,P3&P4 selections
Note:P1,P2,P3&P4 single slections
Example: user select P1=Red next priority P2(selction Options)=White,BLue,Black , simillarly P3&P4
High importance:
one person eligible One Priority, Next Priority he wont come.
User select dynamically.
Output:
Selection Total Count P1(red) 3 P2(white) 2 P3(Blue) 0 P4(Black) 1 Note: Total count should be all Y counts.
Thanks,
Saikiran
Hi, Anonymous
Based on your description, I assume that you want to count based on the priorities. I created data to reproduce your scenario. The pbix file is attached in the end.
Sample:
After unpivot four colors:
Then you may create four calculated tables and a measure as below.
Calculated table: P1 = DISTINCT('Sample'[Attribute]) P2 = DISTINCT('Sample'[Attribute]) P3 = DISTINCT('Sample'[Attribute]) P4 = DISTINCT('Sample'[Attribute]) Measure: Count = IF( HASONEVALUE(P1[Attribute])&& HASONEVALUE(P2[Attribute])&& HASONEVALUE(P3[Attribute])&& HASONEVALUE(P4[Attribute]), IF( SELECTEDVALUE(P1[Attribute])<>SELECTEDVALUE(P2[Attribute])&& SELECTEDVALUE(P2[Attribute])<>SELECTEDVALUE(P3[Attribute])&& SELECTEDVALUE(P3[Attribute])<>SELECTEDVALUE(P4[Attribute])&& SELECTEDVALUE(P4[Attribute])<>SELECTEDVALUE(P1[Attribute]), var t1 = CALCULATETABLE( DISTINCT('Sample'[id]), FILTER( ALL('Sample'), 'Sample'[Attribute]=SELECTEDVALUE(P1[Attribute])&& 'Sample'[Value]="Y" ) ) var t2 = CALCULATETABLE( DISTINCT('Sample'[id]), FILTER( ALL('Sample'), 'Sample'[id] in t1&& 'Sample'[Attribute]=SELECTEDVALUE(P2[Attribute])&& 'Sample'[Value]="Y" ) ) var t3 = CALCULATETABLE( DISTINCT('Sample'[id]), FILTER( ALL('Sample'), 'Sample'[id] in t2&& 'Sample'[Attribute]=SELECTEDVALUE(P3[Attribute])&& 'Sample'[Value]="Y" ) ) var t4 = CALCULATETABLE( DISTINCT('Sample'[id]), FILTER( ALL('Sample'), 'Sample'[id] in t3&& 'Sample'[Attribute]=SELECTEDVALUE(P4[Attribute])&& 'Sample'[Value]="Y" ) ) return SUMX( SUMMARIZE( Selections, Selections[Selections], "Re", SWITCH( SELECTEDVALUE(Selections[Selections]), "P1",COUNTROWS(t1), "P2",COUNTROWS(t2), "P3",COUNTROWS(t3), "P4",COUNTROWS(t4) ) ), [Re] ) ) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You did a great job. Thanks for replying, But there are some issues which this report. I am getting wrong count for P2,P3 and P4. when selecting different colors for priority again count is coming wrong.
Sample data.
1.If user selects dynamically P1,P2,P3 & P4. i am getting P1 value correct but P2,P3 & P4 are having wrong counts.
Sample selection:
Correct count should be:
P1(red)=3
P2(black)=2
P3(blue)=0
P4(white)=1
2. I want to see the counts, If user selects any 1 , 2 or 3 priority. it's not compulsorily that user everytime will select all the priorities. He can select minimum 1 and maximum 4 priority.
Sample selection:
Thanks,
Sai Kiran
Hi, Anonymous
Could you please explain to us the following result? I am not very clear about it. Thanks.
P1(red)=3
P2(black)=2
P3(blue)=0
P4(white)=1
Best Regards
Allan
