DAX to find Average daily volume based on 2 tables

[junaid2107]

Hello Power BI Community

I have the below scenario with 2 tables, The relationship between them is based on State and Date

Table 1:

Date	Location	State	Volume
1st Jan 2022	AAA	X	10
1st Jan 2022	BBB	X	20
1st Jan 2022	CCC	Y	25
2nd Jan 2022	AAA	X	15
2nd Jan 2022	BBB	X	15
2nd Jan 2022	CCC	Y	0

Table 2:

Date	State	Working Day
1st Jan 2022	X	1
1st Jan 2022	Y	1
2nd Jan 2022	X	1
2nd Jan 2022	Y	0

What i want to achieve is Average Daily volume for my date range (which will be a slicer). Explained below

Slicer Selection for Date - 1st to 2nd Jan 2022 (eg.). How could I go about doing this? is it possible through DAX, other solutions that are more efficient is also appreciated

Location	Average Daily Volume
AAA	(10+15)/(1+1) = 12.5
BBB	(20+15)/(1+1) = 17.5
CCC	(25+0)/(1+0) = 25

Regards,

Junaid

[junaid2107]

I am trying to make the relation on a Concatenated field ie. Date+State (1stJan2022X,1st Jan2022Y...). But after joining, im not able to get the Average Volume as i need.

[junaid2107]

The concatenated field would make it a one to many relationship.

[tamerj1]

Hi @junaid2107 
Please try

Average Daily Volume =
SUMX (
    VALUES ( 'Table 1'[Location] ),
    CALCULATE (
        DIVIDE ( SUM ( 'Table 1'[Volume] ), SUM ( 'Table 2'[Working Day] ) ),
        CROSSFILTER ( 'Table 1'[Date+State], 'Table 2'[Date+State], BOTH )
    )
)

[junaid2107]

Hello tamerj1

Im trying out the below, let me know if it makes sense?

• First i create a measure to get Working Days 

Working Days = CALCULATE(SUM('Table 2'[WORKING_DAY]),FILTER('Table 2','Table 2'[State] = VALUES('Table 1'[State])))

• Then i use the above Working Days Measure to get Average Daily Volume
  Average Daily Volume = Sum(Volume)/WorkingDays

[tamerj1]

Hi @junaid2107 
Please try

Average Daily Volume =
SUMX (
    'Table 1',
    DIVIDE (
        'Table 1'[Volume],
        CALCULATE (
            SUM ( 'Table 2'[Working Day] ),
            CROSSFILTER ( 'Table 1'[Date+State], 'Table 2'[Date+State], BOTH )
        )
    )
)

[junaid2107]

Its only working for 1 day selected in my date slicer.

Working Days = CALCULATE(SUM('Table 2'[WORKING_DAY]),FILTER('Table 2','Table 2'[State] = VALUES('Table 1'[State])))

+

Average Daily Volume = Sum(Volume)/WorkingDays

seems to be more robust

[Anonymous · Accepted solution]

Hi @junaid2107 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a calendar table and create relationships with Table 1 and Table 2

2. Create a measure as below to get it:

Average Daily Volume = 
VAR _location =
    SELECTEDVALUE ( 'Table 1'[Location] )
VAR _volumn =
    SUM ( 'Table 1'[Volume] )
VAR _states =
    CALCULATETABLE (
        VALUES ( 'Table 1'[State] ),
        FILTER ( 'Table 1', 'Table 1'[Location] = _location )
    )
VAR _workingday =
    CALCULATE (
        SUM ( 'Table 2'[Working Day] ),
        FILTER ( 'Table 2', 'Table 2'[State] IN _states )
    )
RETURN
    DIVIDE ( _volumn, _workingday )

Best Regards

[junaid2107]

Hello tamerj1

Interesting, It works for 1 category

'Table 1'[Location] 

, but i need to use this measure in another table visual as well and a couple more granular tables

the 2nd table visual has 2 categories

Location, Location Subtype

[tamerj1]

Hi @junaid2107 
My understanding that you have two many to many relationships between the 2 tables. In this case one of them only can be active. Would you please provide more details? Thank you