Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gazsim44
Helper III
Helper III

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

BranchProductAverage shipments per selected period.
AA16
AB21
BA151
BB258

 

Week 2-10

BranchProductAverage shipments per selected period.
AA21
AB22
BA163
BB197

 

Result table

BranchProduct% Difference
AA76%
AB97%
BA93%
BA131%

 

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, 

7 REPLIES 7
V-lianl-msft
Community Support
Community Support

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 )

test_0331_1.PNG

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, 

Groupquery.PNGDataquery.PNGTables.PNGFields.PNGRelationship.PNG

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 ShipmentsAvg. Shipments% Change Shipments
146516352.4%
159617753.3%

Air Comp. Data - weeks 11-13

Total ShipmentsAvg Shipments
453151
29197

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.