Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

TOP N filter within Line and Stacked Column Chart can't filter both lines and columns

Hi everyone,   I have the following Line and Stacked Column Charts (they have the same elements, only filtered by different values) : Chart 1: Column values = Order Quantity of each supplier Lin...
Need Help
Tips and Tricks
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    Please check the steps as below.

     

    1 Create a calcualted column.

     

    Column = IF(Table1[Vendor] IN TOPN(4,VALUES(Table1[Vendor]),CALCULATE(SUM(Table1[Order Quantity]),ALLEXCEPT(Table1,Table1[Vendor]))),Table1[Vendor],"OTHERS")

    2. Create the measures as below.

     

    B = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="B"))
return
IF(ISBLANK(sumt),0,sumt)
    D = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="D"))
return
IF(ISBLANK(sumt),0,sumt)
    E = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="E"))
return
IF(ISBLANK(sumt),0,sumt)
    F = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="F"))
return
IF(ISBLANK(sumt),0,sumt)
    Others = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="OTHERS"))
return
IF(ISBLANK(sumt),0,sumt)

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous,

 

Please check the steps as below.

 

1 Create a calcualted column.

 

Column = IF(Table1[Vendor] IN TOPN(4,VALUES(Table1[Vendor]),CALCULATE(SUM(Table1[Order Quantity]),ALLEXCEPT(Table1,Table1[Vendor]))),Table1[Vendor],"OTHERS")

2. Create the measures as below.

 

B = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="B"))
return
IF(ISBLANK(sumt),0,sumt)
D = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="D"))
return
IF(ISBLANK(sumt),0,sumt)
E = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="E"))
return
IF(ISBLANK(sumt),0,sumt)
F = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="F"))
return
IF(ISBLANK(sumt),0,sumt)
Others = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="OTHERS"))
return
IF(ISBLANK(sumt),0,sumt)

For more details, please check the pbix as attached.

 

Regards,

Frank

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.


Regards,
Frank