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

      • Gazsim44's avatar
        Gazsim44
        Helper III
        6 years ago

        Hi V-lianl-msft ,

         

        Unfortunately I can't get this measure to work in my model.

         

        One major difference is that you are using SUM to calculate shipments where as the data is a list of shipments so requires a DISTINCTCOUNT.  I replaced that part of the measure as per below example but again unfortunately the resulting numbers are not correct.

         

        % Change Shipments = 
var SUM_AIR_Shipments = 
CALCULATE(DISTINCTCOUNT(Air[Shipment Number]),
            FILTER(ALLEXCEPT(Air,Air[Product],Air[Branch Id]),
            Air[Week No.] in VALUES('Air Comp Data'[Week No.])
            )
)
var AIRCOMP_WEEKS = 
    DISTINCTCOUNT('Air Comp Data'[Week No.])
RETURN
    DIVIDE([Avg. Shipments],DIVIDE(SUM_AIR_Shipments,AIRCOMP_WEEKS))

         

        As an example this gives me below % when comparing data which isn't correct. 

        Many thanks, 

         

        Air Data. weeks 2-10

        Total ShipmentsAvg. Shipments% Change Shipments
        146516352.4%
        159617753.3%

        Air Comp. Data - weeks 11-13

        Total ShipmentsAvg Shipments
        453151
        29197