Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Need help with this filter and table link

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 d...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

       

      CategoryFilter by Type VALUE %_RESULTCalculation
      ENROLLED A824  
      OFFEREDA51862518/824*100
      ACCEPTEDA32061320/518*100
      PARTNERA359112359/320*100

       

       

       

      • AllisonKennedy's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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.