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

 

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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 14, 2018

      Hi Mehdi,

       

      Why do you think this DAX measure doesn't work? I've seen nothing wrong on test table.

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

        Hi Sergai,

        The mesure work fine but it did not target Dan needs (Product A from 1 to 3, Product B 20 or more and Dates last 12 months) or am i still missunderstanding the problem ?

        I just adapted your solution so what do you think about this.

        DanNeeds:=IF(CALCULATE((SUM([Product A])>=1 && SUM([Product A])<=3 && SUM([Product B])>=20);Tableau1[Dates] >= TODAY()-365);1;0)

        To test it I had to modify the dataset

         

Resources