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
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
V-lianl-msft's avatar
V-lianl-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Gazsim44 ,

 

What is the formula of [total shipments]?

It is very difficult to analyze without looking at the data and just by imagining. See if you can share the sample pbix.

You could see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

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