Total coming wrong

DAX DAX Calculations
1

I have attached the pbi file, where for Forecasted Payment measure, at the row level values are correct, but for the total value is wrong.
I know it is a common problem, and can be solved using HASONEVALUE(‘Date’[Date].[Month]) and then creating two seperate formulas for total and row level calculations. However , I am not able to get the total calculation right.
Also i want to see only the current month and future dates in my table. In the current table (PBix attached), I am able to see Values for September only if I display August, but I only want September(current month) and future.

Can some one help?
ch.pbix (151.0 KB)

2

HI @Anu,

You should not use SUM when trying to calculate the %, Try using below formula which I modified for CurrentMonthPOC and LastMonthPOC. You can now adjust other measures accordingly.

CurrentMonthPOC = CALCULATE(AVERAGEX('Table','Table'[POC]), FILTER('Date','Date'[Date]))

LastMonthPOC = CALCULATE(AVERAGEX('Table','Table'[POC]),PARALLELPERIOD('Date'[Date].[Date],-1,MONTH))

image
image782×469 48.1 KB

3

But the total forecast payments is 22.57 which is not correct.

4

Hi @Anu

I see where it went wrong, you are trying to calculate percentage and then subtracting those current with lastmonth so it will not give the correct result. Please change the AVERAGEX with SUMX and change the format of all to decimal unless you want to display that too as %(let me know). You will have correct result.

CurrentMonthPOC = CALCULATE(SUMX('Table','Table'[POC]), FILTER('Date','Date'[Date]))

LastMonthPOC = CALCULATE(SUMX('Table','Table'[POC]),PARALLELPERIOD('Date'[Date].[Date],-1,MONTH))

image
image1270×813 86 KB

Also you have mentioned that you want to hide August month, could you please clarify if only hide and unchanged the value i.e. for forecast -32.09?

5

Total for Forecasted Payments should be 8.07+8.21+37.59+40.82+44.67+…(in the above picture)
But now its Total Increment /(1- Total Last Month)*474
ie. 1.00/(1-15.77) * 474
which is not the expected

image

6

Hi @Anu,

May be I didn’t understood the question correctly, sorry for the inconvenience. Will leave for other forum members to answer. Also you can check below which might help you.

7

Here in my case Forecasted Payments values Row-wise is correct, But just the total is incorrect because it is calculating Total Increment /(1- Total Last Month )*474
ie. 1.00/(1-15.77) * 474 = -32.09 which is incorrect.

image

Whereas the correct should be sum of all these,

image

I know it can be implemented using two measures, one when HASONEVALUE(‘Date’[Date].[Month]) is TRUE, and the measure evaluates at row level,
another for total itself when HASONEVALUE(‘Date’[Date].[Month]) is FALSE

But my total measure is not working

8

Hi @Anu,

Please provide the most recent and up to date version of your sample PBIX
Thank you

9

ch.pbix (151.0 KB)

Also i want to see only the current month and future dates in my table. In the current table (PBix attached), I am able to see Values for September only if I display August, but I only want September(current month) and future.

10

Hi @Anu,

First I marked your Date table as Date table.

image
image826×265 17 KB

Next created some logic in a Calculated Column (you can also do this same in PQ M of course)
This allows us to easily Filter the Date table on Current and Future months alone.

image
image736×168 7.24 KB

Created a new Measure using the new CurrentAndFutureMonths attribute as filter

Forecasted payments v2 = 
VAR vTable = 
    ADDCOLUMNS( 
        CALCULATETABLE( VALUES( 'Date'[MonthInCalendar] ), FILTER( ALL( 'Date' ), 'Date'[CurrentAndFutureMonths] = TRUE() )),
        "@Value", CALCULATE( DIVIDE( [Incremental POC],1-[LastMonthPOC])*474 )
    )
RETURN

SUMX( vTable, [@Value] )

with this result

image
image778×302 13.7 KB

Here’s your sample file. eDNA - ch.pbix (127.1 KB)
I hope this is helpful.

3 Likes
11

Few Doubts:

  1. Why should we mark as date table, seems to be working fine without that also?
  2. When can we refer columns in a virtual table.
    For ex: we can refer @Value in SUMX( vTable, [@Value] )
    But not all calculations permits this?
12

#1 - in order for any Power BI time intelligence to work, your date table must be marked as such. I’ve only taken a quick look at this thread, and it doesn’t look like you’re using time intelligence in it, but to allow for the possibility in different visuals, please do; it’s also a best practice. I do this as the first step in all my reports/models. Greg

13

Hi @Anu,

Here’s some background information on your first question…

https://www.ayraanalytics.com.au/mark-as-date-table-feature-in-power-bi-what-and-why/

.

That is correct you can only use iterators in combination with virtual tables.

There is a concept in DAX called “Syntax Sugar”. Simply put, the developers have created simplified versions of more complex formulas to make it easier to learn and use DAX but those can’t be used with virtual tables. Here are some examples.

Syntax Sugar for Total Sales = SUM(Sales[ExtendedAmount])

Full Syntax for Total Sales = SUMX(Sales, Sales[ExtendedAmount])

You can read more on that here:

And I think Brian Grant from CSG found a simple way of visually explaining what DAX is doing

I hope you find this helpful,

1 Like
15

eDNA - ch (1).pbix (126.9 KB)
When we mark as date table won’t default date hierarchy come?
When using date hierarchy created, I am unable to sort in month order.

image