Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 period and then ultimately a percentage difference calculated between both the results if possible.
So for example I would have two tables representing the below setup - each controlled by its own date slicer (using week as a period).
Week 11-13
Branch | Product | Average shipments per selected period. |
A | A | 16 |
A | B | 21 |
B | A | 151 |
B | B | 258 |
Week 2-10
Branch | Product | Average shipments per selected period. |
A | A | 21 |
A | B | 22 |
B | A | 163 |
B | B | 197 |
Result table
Branch | Product | % Difference |
A | A | 76% |
A | B | 97% |
B | A | 93% |
B | A | 131% |
My average measure is currently set as.
Avg. Shipments = AVERAGEX(VALUES('Date'[Week No.]),[Total Shipments])
I have setup a duplicate of the fact data in a hope that this may work which yes will give me the average in each selected date period but then I have no way of completing the comparison and obtaining any percentage difference.
Many thanks for any assistance you can provide,
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.
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
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.
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,
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.
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 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 |
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |