Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
I want to visualize weekly date vs weekly salary and average weekly salary using line & clustered column chart
Planning to have visuals like below
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
Solved! Go to Solution.
Hi @bourne2000
Is this the result you want ?
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.
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.
Hi @bourne2000
Is this the result you want ?
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.
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.
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! |
You can get your x-axis values with an End Of Week column on your Date table with this expression
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |