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

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
Charlie_Miller
Frequent Visitor

How to get total to calculate differently from rows

I have a measure that extrapolates the total sales quantity for the most recent / partial month of data.  

It looks like this:

Total QTY Ext = 


VAR NumOfBusinessDays = CALCULATE(COUNTROWS('Calendar Table'),
                                                         AND('Calendar Table'[Is Workday] = TRUE(), ISBLANK('Calendar Table'[Holiday Desc]) = TRUE()))


VAR MaxFactDate = MAX('fact table' [Date_Column])

VAR NumOfBusinessDaysComplete = CALCULATE(COUNTROWS('Calendar Table'),
                                                          AND('Calendar Table'[Is Workday] = TRUE(), ISBLANK('Calendar Table'[Holiday Desc]) = TRUE()),
                                                          'Calendar Table'[Date Column] <= MaxFactDate)
RETURN
DIVIDE([Total QTY], NumOfBusinessDaysComplete) * NumOfBusinessDays
------------------------------------------------------------------------------------------------------------------------------------------------


I have another typical measure: Total QTY = SUM('fact table' [Sales QTY])
I included that measure in the visual below for reference/comparison

[Total QTY Ext] returns the following example results on a table visual:

Month & Year              Total QTY Ext              Total QTY
Jan '24                            100                            100
Feb '24                           150                             150
Mar '24                           125                            125
Apr '24                           120                             120
May '24                          175                             175
Jun '24                           200                              43
TOTAL                            1,178                           713

The purpose of the measure is to be equal to the normal Total QTY measure except for the current month where we only have partial data in which it extrapolates the data to predict where we will end up. The measure accurately does this, but the total is off. 

How can I keep the aspects of the measure which work but correct the calculation for the total?  


1 ACCEPTED SOLUTION

@Charlie_Miller Always hard to work on these with specificity without sample data to recreate the problem. I'm not sure what you are doing in your attempt there. Generally, you recreate the table visual virtually using SUMMARIZE or GROUPBY along with your original measure (the one that works on rows). You stuff that into a table VAR. You wouldn't use HASONEFILTER, you would use HASONEVALUE and make it something in your table visual ( a column that you use in the table). If there is one value, you return the original measure as you are in a row. If there is more than one value then you are in a total row and you SUMX across your virtual table.



Follow on LinkedIn
@ 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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Community Champion
Community Champion

@Charlie_Miller First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

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

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8



Follow on LinkedIn
@ 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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Thanks Greg, I did go ahead and vote on the idea as you suggested. I had previously done some searching and had no luck trying a few different things. For example, here is something I tried that did not work: 

RETURN
If(HASONEFILTER('Calendar Table'[Date Column]), DIVIDE([Total QTY], NumOfBusinessDaysComplete) * NumOfBusinessDays, [Total QTY])

This made the extrapolation portion of my measure not work and it calculated the same as Total QTY for all rows. Do you have any suggestions on how I can fix my measure?


@Charlie_Miller Always hard to work on these with specificity without sample data to recreate the problem. I'm not sure what you are doing in your attempt there. Generally, you recreate the table visual virtually using SUMMARIZE or GROUPBY along with your original measure (the one that works on rows). You stuff that into a table VAR. You wouldn't use HASONEFILTER, you would use HASONEVALUE and make it something in your table visual ( a column that you use in the table). If there is one value, you return the original measure as you are in a row. If there is more than one value then you are in a total row and you SUMX across your virtual table.



Follow on LinkedIn
@ 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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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