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

Gazsim44's avatar
Gazsim44
Icon for Helper III rankHelper 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
    Icon for Community Support rankCommunity 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
      Icon for Helper III rankHelper 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