Forum Discussion

Gazsim44's avatar
Gazsim44
Icon for Helper III rankHelper III
6 years ago

Dynamic Percentage Change with two tables

Hi All, 

 

I am currently attempting to set up two tables from the same fact data but in turn have each table controlled by its own date slicer so an average can be shown for shipments during that period and then ultimately a percentage difference calculated between both the results if possible.

 

So for example I would have two tables representing the below setup - each controlled by its own date slicer (using week as a period).

 

Week 11-13

BranchProductAverage shipments per selected period.
AA16
AB21
BA151
BB258

 

Week 2-10

BranchProductAverage shipments per selected period.
AA21
AB22
BA163
BB197

 

Result table

BranchProduct% Difference
AA76%
AB97%
BA93%
BA131%

 

My average measure is currently set as.

 

 

Avg. Shipments = AVERAGEX(VALUES('Date'[Week No.]),[Total Shipments])

 

 

 

I have setup a duplicate of the fact data in a hope that this may work which yes will give me the average in each selected date period but then I have no way of completing the comparison and obtaining any percentage difference.

 

Many thanks for any assistance you can provide, 

Need Help

7 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Gazsim44 ,

     

    If you have two identical tables and use [week. No] in the tables as slicers, you can calculate [avg.shipments] of Table 2 in Table 1 based on the slicer value of Table 2.

    Try the dax like below:

    Measure1 =
VAR avg_table2_shipments =
    CALCULATE (
        AVERAGEX (
            FILTER (
                ALLEXCEPT ( table1, table1[Branch], table1[Product] ),
                table1[Week No.] IN VALUES ( 'table2'[Week No.] )
            ),
            CALCULATE ( SUM ( table1[Shipments] ) )
        )
    )
RETURN
    DIVIDE ( [Avg. Shipments], avg_table2_shipments )

    Here is the sample pbix.

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Gazsim44's avatar
      Gazsim44
      Icon for Helper III rankHelper III
      6 years ago

      Hi V-lianl-msft Liang, 

       

      Many thanks for your reply. What I should have mentioned in my original post in hindsight is that the week is coming from my Date table and that the Branch is coming from a 3rd table and is linked to both other tables by means of a relationship.

       

      This would naturally I guess change the measure you have already built, 

       

      Many thanks

      • V-lianl-msft's avatar
        V-lianl-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Gazsim44 ,

         

        Could you please share the sample pbix through cloud service like OneDrive for business?

        (Please mask any sensitive data before uploading)

         

        Best Regards,
        Liang
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.