Forum Discussion

Dan Cloutier's avatar
Dan Cloutier
Copper Contributor
Feb 09, 2018

Pivot Table or some other way to solve this issue?

Problem: I want to create a list of all accounts that have ordered 1-3 pallets in the last 12 months of Product A  but have also ordered 20 or more pallets of Product B in the last twelve months.   W...
BI & Data Analytics
Need Help
Mehdi HAMMADI's avatar
Mehdi HAMMADI
Brass Contributor
Feb 12, 2018

I created a dataset with 4 fields [Accounts], [Dates], [Product A] and [Product B]
and I applied the transformation below using "Get & Transform" (Power Query).

Not sure it is the best way but try it and let me know :

let
   Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Accounts", type text}, {"Dates", type datetime}, {"Product A", Int64.Type}, {"Product B", Int64.Type}}),
   #"Apply Conditions" = Table.AddColumn(#"Changed Type", "Keep", each if ([Product A] >=1 and [Product A] <=3 and [Product B]>=20 and [Dates] >= Date.AddYears(DateTime.LocalNow(),-1) ) then 1 else 0),
   #"Keep Good Records" = Table.SelectRows(#"Apply Conditions", each ([Keep] = 1)),
   #"Removed Duplicates" = Table.Distinct(#"Keep Good Records", {"Accounts"})
in
   #"Removed Duplicates"


Hope this will help
Regards
Mehdi

JKPieterse's avatar
JKPieterse
Silver Contributor
Feb 13, 2018
I'm sorry if I didn't make myself clear, I was wondering if you could share some example records of the data you are trying to summarize, not of the query itself. As I do not have access to your data the query does not help much :-)
  • Mehdi HAMMADI's avatar
    Mehdi HAMMADI
    Brass Contributor
    Feb 13, 2018

    Hi Jan,

    I fully understand your answer to Dan and it seems to me that it is up to him to provide a sample of data to solve his problem.
    Not providing data, I imagined a dataset that could do the trick.
    In the example in attachment, you will find the starting data and the result and I have commented the query.

    Let me know if that was helpfull

     

    • Mehdi HAMMADI's avatar
      Mehdi HAMMADI
      Brass Contributor
      Feb 14, 2018
      Hi
      Sorry but the sollution I proposed is out of contexte.
      The result is a list accounts that had at least placed an order with 1-3 pallets of product A and 20 pallets or more of product B during the last 12 months.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 14, 2018

      Here are accounts and dates when clients ordered 1-3 of A and less than 20 of B. If the task is to select accounts who ordered 1-3 of A and <20 of B totally for entire year, when that could be a Pivot Table if added it to data model when creating, and a measure like

      =IF(CALCULATE(AND(SUM([Product A])<=100,SUM([Product B])<=200),Tableau1[Dates] >= TODAY()-365),1,0)

      (no initial combination in the sample table, thus i took another figures), after that filter value in pivot table where that measure =1. As attached.

      • Mehdi HAMMADI's avatar
        Mehdi HAMMADI
        Brass Contributor
        Feb 14, 2018

        Hello,
        When reading your answer I just realized that I had misunderstood Dan's question.
        I understood that the customer had at least placed an order with 1-3 pallets of product A and 20 pallets or more of product B during the last 12 months. which means that my answer is out of context.
        While it is a question of the sum of the orders of product A and product B for the last 12 months

        But I'm not sure that the formula you posted can solve the issue

Resources