Forum Discussion
DAX calculation
From a single Fact table I have created two measures from two different column , one is "Total Staff Attendance"=Distinctcount(Table1[Name&Date]), another measure is "NewDC"=Average(Table1[TotalDesk]) , please see the attached screenshort, I want to show in a monthly line chart how many days in a month the "Total Staff Attendance" is greater then "NewDC", the result will be 2 for February ( i.e. 7 , 9th February)
10 Replies
can you clarify further?
(a) Calculate Total Staff Attendance as distinct count for a day and sum of each day at month level
(or) distinct count for a day ; for a month
(b) Calculate NewDC as average for a day and sum of each day at month level
(or) average for a day ; for a month
(c) Diff of (a) - (b) at day level and at month level
[a] - [b]
Formula varies based on your needs!
Some links to tell what I am asking ...
https://www.youtube.com/watch?v=UcJ-pgPp5Bo
https://www.powertechtips.com/calculate-monthly-averages-power-bi/
Depends on what you like
Total Staff Attendance = SUMX( VALUES ( 'DimDate'[MonthName] ), Distinctcount(Table1[Name&Date]))
NewDC = AverageX ( Values( 'DimDate'[MonthName]) , Table1[TotalDesk])
Diff Calc = [Total Staff Attendance] - [NewDC]Thank you for your reply, I have used your measure and get the difference just I need to know how many days in a month the difference is positive( it is in monthly, the result will 2 days in February)
(a) Calculate Total Staff Attendance as distinct count for a day ; for a month
(b) Calculate NewDC as average for a day ; for a month
(c) Diff of (a) - (b) at month level
[a] - [b]
Can you share your full DAX syntax? (instead of picture)
Try this and see if it helps
Positive Diff Calc Count = SUMX( VALUES ( 'DimDate'[MonthName] ), If( [Diff Calc] >= 0, 1, 0))
Hi,
Assuming the Dates on the X-axis have been dragged from the Calendar Table, write this measure
=countrows(filter(values(calendar[Date]),[Total staff atendance]>[NewDC]))
Hoep this helps.
Thank you for your reply, dates on the X-axis have been dragged from the fact table( calender table have no date column , only month column is availavle)
That is not how it should be. In the Calendar Table, there should always be a Date column. All Date fields such as year, month etc. should be created in this Calendar Table and to all visuals, date related fields should be dragged from the Calendar Table.
