Forum Discussion
Need help with this filter and table link
Community Championamitchandak Your result works, but I think the issue is that it won't filter by Type. Anonymous Please let me know if this assessment of problem is correct? :
Table 1 has 5 Types (A, B, C, D, E) for each Index, and if I'm understanding your question correctly, you want to filter the % calculation by Type. Since Table 3 has no reference/link to Type, and each type is present for each index, it won't give any different results when slicer selection changes for Type.
Anonymous Maybe you can try to explain your problem again and show example of your desired results?
AllisonKennedy Sorry forgeting my desires result. my example for the result filter by A (example) . This table colum Category should base on the order table, Table 2
| Category | Filter by Type | VALUE | %_RESULT | Calculation |
| ENROLLED | A | 824 | ||
| OFFERED | A | 518 | 62 | 518/824*100 |
| ACCEPTED | A | 320 | 61 | 320/518*100 |
| PARTNER | A | 359 | 112 | 359/320*100 |
- AllisonKennedy
Anonymous Try a series of MEASURES:
Enrolled Value = CALCULATE(SUM(Table[Value]), Table[Category]="ENROLLED")
Offered Value = CALCULATE(SUM(Table[Value]), Table[Category]="OFFERED")
Accepted Value = CALCULATE(SUM(Table[Value]), Table[Category]="ACCEPTED")
Partner Value = CALCULATE(SUM(Table[Value]), Table[Category]="PARTNER")
% = SWITCH(SELECTEDVALUE(Table[Value]),
"OFFERED", DIVIDE([Offered Value], [Enrolled Value]),
"ACCEPTED", DIVIDE([Accepted Value], [Offered Value]),
"PARTNER", DIVIDE([Accepted Value], [Offered Value]),
""
)Then create Matrix visualization with:
Table[Category] in Rows
Table[Value] in Values (with SUM aggregation)
[%] in Values
Add slicer/filter for Type.
I learn a lot in the last 3 days about Power BI. It is awesome, Thank you AllisonKennedy .. for your help. Yes it would work for matrik visualization. How about for Line Clustered column Chart?
- AllisonKennedy
Anonymous Do you want line clustered column combo chart? That should work with the measures, just leave column series empty and put all the measures in either line or column values. If that doesn't make sense, please draw a pic of what you want/need.