Forum Discussion

Gazsim44's avatar
Gazsim44
Helper 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 ...
Need Help
V-lianl-msft's avatar
V-lianl-msft
Community 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
Helper 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
    Community 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.

    • Gazsim44's avatar
      Gazsim44
      Helper III
      6 years ago

      Hi V-lianl-msft ,

       

      I have created a test file but unfortunately our organisation does not allow sharing of files on One Drive! I have therefore included some screenshots which I hope will assist going forward?

       

      Thanks, 

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

        Hi Gazsim44 ,

         

        Create new [week. No] calculated columns in two tables and use them as  slicers.

        WEEK.NO = WEEKNUM(DATA1[date],2)

        Create a measure with the following DAX

         

        Measure1 =
VAR SUM_DATA2_shipments =
    CALCULATE (
        SUM ( DATA1[Shipments] ),
        FILTER (
            ALLEXCEPT ( DATA1, DATA1[ID], DATA1[Product] ),
            DATA1[WEEK.NO] IN VALUES ( DATA2[WEEK.NO] )
        )
    )
VAR DATA_2_WEEKS =
    DISTINCTCOUNT ( DATA2[WEEK.NO] )
RETURN
    DIVIDE ( [Avg. Shipments], DIVIDE ( SUM_DATA2_shipments, DATA_2_WEEKS ) )

         

        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.