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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
dbrandone
Helper III
Helper III

Help. Numbers are not adding up and I do not know why

In the picture below, you can see the measure that wrote for Consent rate Last Week. In the matrix below, I have year, months and week number in the row part and then columns. The consent rate is simply the divide function between the other two columns. I put the consent rate measure I created into a measure to get last weeks numbers and they are not matching up. I cannot seem to figure out how these numbers are off.

 

dbrandone_0-1630523657182.png

 

1 ACCEPTED SOLUTION

@dbrandone I did invent a DAX week number column that does not reset at the end of the year called Sequential: Sequential - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

12 REPLIES 12
Fowmy
Super User
Super User

@dbrandone 

Please the following measure:

VAR __WEEK = WEEKNUM ( TODAY () , 2)-1
RETURN

CALCULATE (
    [Consent Rate],
    'Date'[Week of Year] = __WEEK
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy 

 

That got me the same result.

dbrandone_0-1630524651275.png

 

@dbrandone This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

What is also odd is that I wrote a measure for last month and the number was right.

 

dbrandone_0-1630527034667.png

 

Thanks @Greg_Deckler ,

I went into the table view and manually filtered the data based on the filters in the measures for the totals measures that led to the consent rate. I did the math and they line up perfectly with the table. The table is correct, but the card measure is not. 

@dbrandone What is result you want in your card visual?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

It needs to show 65.00%(Last Week, Week # 35)

@dbrandone OK, I can think of a few things. One, check what filters are active on your matrix visual and card visual. Are they the same? Two, try hard-coding the week to 35 and see if you get the right answer. Three, do you have any prior or future years that it could be pulling week 35 from? Fourth, is there a data issue where week 35 is also in some other month


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

 

The filters for both the matrix and the card were set to "ALL" so there were no impediments there. Obviously, the matrix had more filters in the visual with the more components it had vs. the card, but there were no visual filters causing an issue.

 

Interesting though, when I hard code week 35 and year 2021 into each variable, the card percentage comes out right. The weird thing about it is that I went back to all the years in the matrix with week 35 and did a manual calculation and it still does equal the 60.38%. That is of course if it is pulling the data from all week 35's in all years. So it seems that it is something with the Week number column in the date table? Is it beneficial to have a week number column in the date table that goes up, but doesn't reset at the beginning of the year?

 

dbrandone_0-1630592359341.png

 

@Greg_Deckler 

 

I ended up trying this and it seemed to have worked and should keep steady this way, but I am still interested in the reason my Week number column isn't functioning properly or if there is an issue with one of my tables. 

 

dbrandone_1-1630592784918.png

 

@dbrandone I did invent a DAX week number column that does not reset at the end of the year called Sequential: Sequential - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

 

Thanks!! I already created a new column off of your column code and it is functioning well. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors