Forum Discussion
Helper IIIDynamic Percentage Change with two tables
Community SupportHi 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.
Helper IIIHi 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 Shipments | Avg. Shipments | % Change Shipments |
| 1465 | 163 | 52.4% |
| 1596 | 177 | 53.3% |
Air Comp. Data - weeks 11-13
| Total Shipments | Avg Shipments |
| 453 | 151 |
| 291 | 97 |
- V-lianl-msft
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.