Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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?
Solved! Go to 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.
@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
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.