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
bourne2000
Helper V
Helper V

Calculating weekly salary and average weekly salary?

hHi

 

I am having data which has date, total salary. I created date table. I wanted to calculate weekly average salary and average weekly salary

bourne2000_1-1635032570155.png

 

I want to visualize weekly date vs weekly salary and average weekly salary using line & clustered column chart

 

Planning to have visuals like below

 

bourne2000_0-1635032388098.png

 

 

I checked other posts for calculate weekly and average weekly but not successful. My week starts from Sunday. I don't know how to calculate average weekly salary and weekly salary. Can anyone please advise?

 

SAMPLE PBIX FILE https://we.tl/t-co9MTChVvu

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

Hi @bourne2000 

 

Is this the result you want ?

vchenwuzmsft_0-1635228761605.png

 

If it is , please create two measures as the following :

 

 

weekly salary =
VAR _if =
    MAX( 'Date'[SortDayName] )
VAR _s =
    CALCULATE(
        SUM( Data[Total Salary] ),
        FILTER( ALL( 'Date' ), [WeekNo] = MAX( 'Date'[WeekNo] ) )
    )
RETURN
    IF( _if = 6, _s, BLANK() )

 

 

average weekly salary =
VAR _if =
    MAX( 'Date'[SortDayName] )
VAR _a =
    CALCULATE(
        AVERAGE( Data[Total Salary] ),
        FILTER( ALL( 'Date' ), [WeekNo] = MAX( 'Date'[WeekNo] ) )
    )
RETURN
    IF( _if = 6, _a, BLANK() )

 

 

And set the X axis type as Categorical, this will let the x axis show the 7/18/2021, 7/25/2021 and so on rather than consecutive dates.

vchenwuzmsft_1-1635228761609.png

I put the pbix file in the end you can refer.

 

Best Regards

Community Support Team _ chenwu zhu

 

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

View solution in original post

3 REPLIES 3
v-chenwuz-msft
Community Support
Community Support

Hi @bourne2000 

 

Is this the result you want ?

vchenwuzmsft_0-1635228761605.png

 

If it is , please create two measures as the following :

 

 

weekly salary =
VAR _if =
    MAX( 'Date'[SortDayName] )
VAR _s =
    CALCULATE(
        SUM( Data[Total Salary] ),
        FILTER( ALL( 'Date' ), [WeekNo] = MAX( 'Date'[WeekNo] ) )
    )
RETURN
    IF( _if = 6, _s, BLANK() )

 

 

average weekly salary =
VAR _if =
    MAX( 'Date'[SortDayName] )
VAR _a =
    CALCULATE(
        AVERAGE( Data[Total Salary] ),
        FILTER( ALL( 'Date' ), [WeekNo] = MAX( 'Date'[WeekNo] ) )
    )
RETURN
    IF( _if = 6, _a, BLANK() )

 

 

And set the X axis type as Categorical, this will let the x axis show the 7/18/2021, 7/25/2021 and so on rather than consecutive dates.

vchenwuzmsft_1-1635228761609.png

I put the pbix file in the end you can refer.

 

Best Regards

Community Support Team _ chenwu zhu

 

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

CNENFRNL
Community Champion
Community Champion

Screenshot 2021-10-24 074649.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

mahoneypat
Employee
Employee

You can get your x-axis values with an End Of Week column on your Date table with this expression

 

EOW = 'Date'[Date] - WEEKDAY('Date'[Date]) + 1
 
But please clarify what you mean by weekly average and average weekly.  Show expected values in your chart pic or list them out.
 
Pat
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.