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

Line values = Average TAT of each supplier

 

Chart 2:

Column values = Unit Price of each supplier

Line values = Average Net Order Cost of each supplier

 

* Since the 2 graphs are supposed to work and function the same. I will just describe what data I've got for one chart.

 

 

*Data Source = Excel

 

Here are some other data I used for my chart:

 

Shared axis: POReceivedDate (Data TAT table)

Note: Sorted by Year-Quarter-Month Date Hierarchy

 

Column series: Big Supplier Names (Data TAT table)

Note: In Excel, I manually put 12 biggest supplier names (taken from Vendor column which has all supplier names) based on Order Quantity and Net Order Cost in a column named Big Supplier Names and the rest of the suppliers as "Others".

 

Column values: Order Quantity (Data TAT table)

 

Line values: Average TAT of those 12 mentioned suppliers ; Average TAT of Others

Note: I use the following measure to calculate Average TAT of each supplier.

 

 

Average TAT - Supplier A = 
CALCULATE(
	AVERAGE('Data TAT'[TATclosed]),
	'Data TAT'[Big Supplier Names]
		IN { "Supplier A" }
)

 

 

I also have a slicer that looks something like this:

 

What it does is that the graph above will respond accordingly to the type(s) being selected in this slicer.

 

I'd like for each/all type(s) selected, the graph only shows Top 4 Suppliers and Others by Quantity Order.

 

So, in the Visualisations pane -> FILTERS -> Visual level filters -> Big Supplier Names -> Filter TOP 5 by Order Quantity

 

 

 

Does anyone know how I can filter my Top 5 Suppliers (or Top 4 suppliers and Others) by Order Quantity and the graph only shows TAT lines corresponding to the Top 5?

 

Thank you very much!

 

 

 

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

6 Replies

  • 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

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

    Hi Anonymous,

     

    Kindly share your sample data and excepted result to me.

     

    Regards,

    Frank

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Frank,

       

      Here are my sample data:

       

       

      Note: The original data doesn't have column B. I created column B (Big Supplier Names by Net Order Quantity) because I couldn't find a way to have my chart displayed TOP 4 and Others. Again, this is time consuming and I might have to repeat this step everytime I receive a new dataset to see who are my Top 4 Suppliers and set the rest of the suppliers to be "Others", which then makes Top 4 and Others.

       

      Here is my expected result:

       

       

       

       

      I have this slicer which was created by "Type" column.

       

       

      I'd like my graph to only show TOP 4 and OTHERS with their corresponding TAT lines whenever a selection (either single or multiple option(s)) is made in the "Select Type" slicer.

       

      *Column values = Order Quantity

      *Line values = TAT

       

      And is it possible to get the graph worked out the way I want without manually setting certain suppliers as Big Suppliers and the rest as Others in Excel?

       

      Thank you so much!!!

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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

        Line values = Average TAT of each supplier

         

        Chart 2:

        Column values = Unit Price of each supplier

        Line values = Average Net Order Cost of each supplier

         

        * Since the 2 graphs are supposed to work and function the same. I will just describe what data I've got for one chart.

         

         

        *Data Source = Excel

         

        Here are some other data I used for my chart:

         

        Shared axis: POReceivedDate (Data TAT table)

        Note: Sorted by Year-Quarter-Month Date Hierarchy

         

        Column series: Big Supplier Names (Data TAT table)

        Note: In Excel, I manually put 12 biggest supplier names (taken from Vendor column which has all supplier names) based on Order Quantity and Net Order Cost in a column named Big Supplier Names and the rest of the suppliers as "Others".

         

        Column values: Order Quantity (Data TAT table)

         

        Line values: Average TAT of those 12 mentioned suppliers ; Average TAT of Others

        Note: I use the following measure to calculate Average TAT of each supplier.

         

         

        Average TAT - Supplier A = 
CALCULATE(
	AVERAGE('Data TAT'[TATclosed]),
	'Data TAT'[Big Supplier Names]
		IN { "Supplier A" }
)

         

         

        I also have a slicer that looks something like this:

         

        What it does is that the graph above will respond accordingly to the type(s) being selected in this slicer.

         

        I'd like for each/all type(s) selected, the graph only shows Top 4 Suppliers and Others by Quantity Order.

         

        So, in the Visualisations pane -> FILTERS -> Visual level filters -> Big Supplier Names -> Filter TOP 5 by Order Quantity

         

         

         

        Does anyone know how I can filter my Top 5 Suppliers (or Top 4 suppliers and Others) by Order Quantity and the graph only shows TAT lines corresponding to the Top 5?

         

        Thank you very much!

         

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

    I am trying to display line and stack column chart using two different data sources. Productivity row from first table as line values and task type from below table as column values. But it seems like productivity line is showing as total values 1076 as a straight line instead of showing breakdown by daily data as in the first table. Is it possible to make productivity data shows as a breakdown daily data instead of showing total value in line and stack column chart? First table and second table is from different data sources and cannot find relationship between these two tables. Please advise. Thank you in advance.