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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Turf03
Helper II
Helper II

Sum of a daily measure at a monthly level

I have a measure that creates a score at the daily level for employees. I would like to sum the daily measure at the monthly level but when attempting to do so, it bases score on the monthly totals.

 

I dont know what I'm missing here???

1 ACCEPTED SOLUTION

Hi @Turf03 - I think this is a modeling issue. I just set this up and it just works. Let me walk you through what I did.

  1. I created a date table. I use this one. Full instructions there on how to get that into Power Query. But you will need to change the first Source line to this:
    1. = {Number.From(#date(2020,1,1))..Number.From(#date(2020,12,31))}
    2. If you want to make it dynamic so it moves in time with new data you get, see this article. Creating a Dynamic Date Table in Power Query
  2. I marked it as a Date Table in the desktop. Just right-click on it in the Report view, Mark as Date Table, Mark as Date Table, then select the date column.
    1. edhans_0-1602623298933.png

       

    2. Turn off Automatic date/time intelligence in Options. You have a real date table now and do not need the mini-date tables Power BI generates.
  3. Ensure the date table is related to your data table. It should be a 1 to many from the date table to your table with the filter pointing to your table.
    1. 2020-10-13 14_10_44-20201013 - Monthly Total with Date Table - Power BI Desktop.png
  4. Added a new measure for total points:
    1. Total Points = SUM('Table'[Points])
  5. Created a table and dropped the "Month Name" field from the Date table, Employee name, and Total Points measure.

That gave me the above image that shows Bob with 2 points.

 

Here is my PBIX file if you want to play with it.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

You are going to need to share some data using the links below, and show expected output. This has to do with the filter being applied, but without some info, it is vitually impossible to determine your specific issue.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Basically every day, depending on employee performance, they receive a points for meeting all of their targets.

The points column is a measure Points = If(Met Targets = "Yes",1,0)

 

Whats happening, if I create a table that is a monthly wrap up, looking at the total monthly column (Met Targets) and then giving them points based on that instead of the total month.

 

So based on the sample below, instead of 2 points for January, its giving the employee 1 point.

 

DateEmployeeMet TargetsPoints
1/1/2020BobYes1
1/2/2020BobYes1
1/3/2020BobNo0

 

 

Hi @Turf03 - I think this is a modeling issue. I just set this up and it just works. Let me walk you through what I did.

  1. I created a date table. I use this one. Full instructions there on how to get that into Power Query. But you will need to change the first Source line to this:
    1. = {Number.From(#date(2020,1,1))..Number.From(#date(2020,12,31))}
    2. If you want to make it dynamic so it moves in time with new data you get, see this article. Creating a Dynamic Date Table in Power Query
  2. I marked it as a Date Table in the desktop. Just right-click on it in the Report view, Mark as Date Table, Mark as Date Table, then select the date column.
    1. edhans_0-1602623298933.png

       

    2. Turn off Automatic date/time intelligence in Options. You have a real date table now and do not need the mini-date tables Power BI generates.
  3. Ensure the date table is related to your data table. It should be a 1 to many from the date table to your table with the filter pointing to your table.
    1. 2020-10-13 14_10_44-20201013 - Monthly Total with Date Table - Power BI Desktop.png
  4. Added a new measure for total points:
    1. Total Points = SUM('Table'[Points])
  5. Created a table and dropped the "Month Name" field from the Date table, Employee name, and Total Points measure.

That gave me the above image that shows Bob with 2 points.

 

Here is my PBIX file if you want to play with it.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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