Need help with this filter and table link

[Anonymous]

Hello all,

I have a bit problem well for me that still new in this power BI (I used to use with Tableau) , since I had to migrate to Power BI, I have learning this Power BI that it is completly different than Tableau.   Anyway, I need help. please hep.  

I have 3 tables: 

Table 1:   

Index        Category            Type                   Value

1	ENROLLED	A	824
1	ENROLLED	B	0
1	ENROLLED	C	0
1	ENROLLED	D	0
1	ENROLLED	E	0
2	OFFERED	A	518
2	OFFERED	B	114
2	OFFERED	C	104
2	OFFERED	D	175
2	OFFERED	E	800
3	ACCEPTED	A	320
3	ACCEPTED	B	57
3	ACCEPTED	C	35
3	ACCEPTED	D	56
3	ACCEPTED	E	334
4	PARTNER	A	359
4	PARTNER	B	85
4	PARTNER	C	37
4	PARTNER	D	57
4	PARTNER	E	301

Table 2: (I create this table using Power BI for Customize/Manual Sort)

Category	Sort
ENROLLED	1
OFFERED	2
ACCEPTED	3
PARTNER	4

Table 3:  (I created this table using Power BI, with formula for %_Column =  IF('Table3'[Index]=1; BLANK();
'Table3'[Summary]/MAXX(FILTER('Table3';'Table3'[Index]=EARLIER('Table3'[Index])-1);'Table3'[Summary)*100)

This formula is to achive value in %_Column  - (1711/824)*100  207.., (802/1711)*100

Index	CATEGORY	SUMMARY	%_Column
1	ENROLLED	824
2	OFFERED	1711	207
3	ACCEPTED	802	46
4	PARTNER	839	104

Problems:

When I did filter base on Type in Table 1:  A or B or C or D or E.   It doesn work or did not show resul in the chart.  (I am using bar chart). I know there is somehting wrong with the link or filter.  

How to achive so the filter would work?  pleaseeee help.   

Any help will much appreciated it!   

Thank you in advance.  

EH

[v-xicai]

Hi  @Anonymous  ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[Anonymous]

I will get back to you,, been bussy lately.  thank youuu..

[v-xicai]

Hi @Anonymous ,

You couldn't have a calculated table or column interacted with a slicer or interaction , since calculated table or columns are only calculated when data is loaded/refreshed. 

To achieve your demand, you may needn't to create another calculated table3, just create measure like DAX below in Table1, then display Table1[Category], Table1[Type], Table1[Value] and Table1[%_Result] in table visual.

%_Result= 

Var _LastValue=  CALCULATE(SUM('Table1'[Value]),FILTER(ALLSELECTED('Table1'),'Table1'[Category]=MAX('Table1'[Category]) &&'Table1'[Type]=MAX('Table1'[Type])&&'Table1'[Index]=MAX('Table1'[Index])-1))

Var _Percent= DIVIDE( MAX('Table1'[Value]), _LastValue )*100

Return

IF(_LastValue<> BLANK(), _Percent , 0)

Best Regards,

Amy 

Community Support Team _ Amy

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

[Anonymous]

Hi Amy,  thank you, I learn a lot in the past 3 days about Power BI,  I will try the formula that you given,  again much appreciated.  

[amitchandak]

@Anonymous , As a new column this should have worked

try like

%_Column =
'Table3'[Summary]/MAXX(FILTER('Table3';'Table3'[Index]=EARLIER('Table3'[Index])-1);'Table3'[Summary])*100

Will work if Table 3 is created like this

Table 3 = summarize(Table1, Table1[index],Table[Category],"Value",sum(Table[Value]))

[AllisonKennedy]

@amitchandak  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?

[Anonymous]

@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.

[Anonymous]

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.

[Anonymous]

I will try first and let you know.  Thanks

[Anonymous]

@AllisonKennedy  yes it is what you said that I am going to achieve thank you for interate me for me, my English is not eloqunce enough to explained it

"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]

yes, @AllisonKennedy exactly what you said... that what I encounter the problem.  What should I do?   thank you

[AllisonKennedy]

@Anonymous 

Lots going on here. First, you need to understand the relationships between these tables, which you have not described. 

Table3 is a SUMMARY table, so you cannot filter it by type, because it is at a higher level and has no reference to Type.

What are you exactly trying to achieve? Do you just have the 4 indexes? If so, you could just create a MEASURE for their totals and then create measure or measures to calculate the percentages.