topic Re: how to sum fo the prepared measure in the total in DAX Commands and Tips

how to sum fo the prepared measure in the total

mic_rys — Mon, 31 Jan 2022 13:50:18 GMT

Hi, I have a problem with total in my prepared measure, I know that need to user hasonevalue function but my all ideas doesn't work.

My measure:

Fill blank rows 2 =

VAR PrevDate =

MAXX (

FILTER (

ALL ( 'Date' ),

'Date'[Date] < MAX ( 'Date'[Date])

&& NOT ( ISBLANK ( [Fill blank rows first] ) )

'Date'[Date]

)

VAR LastValueFill =

CALCULATE (

[Measure1],

FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] = PrevDate )

)

VAR FillEmpty = if( ISBLANK([Fill blank rows first]), LastValueFill,[Fill blank rows first] )

return

IF (

HASONEVALUE ( Clients[Code] ),

FillEmpty

,BLANK()

)

In short, this measure inserts the last values if blank.

Instead of Blank in Total I need a sum of the FillEmpty.

Result:

Re: how to sum fo the prepared measure in the total

amitchandak — Mon, 31 Jan 2022 14:42:35 GMT

mic_rys , Change the return like

sumx( addcolumns(summarize( Table, Clients[Code] , 'Date'[Year Month]), "_1", calculate(IF ( HASONEVALUE ( Clients[Code] ), FillEmpty ,BLANK() ))), [_1])

Re: how to sum fo the prepared measure in the total

mic_rys — Mon, 31 Jan 2022 14:55:47 GMT

amitchandak

I tried this way but it doesn't sum correctly:

Re: how to sum fo the prepared measure in the total

amitchandak — Mon, 31 Jan 2022 16:34:00 GMT

mic_rys ,

can you simply keep this in return

sumx( addcolumns(summarize( Table, Clients[Code] , 'Date'[Year Month]), "_1", calculate(IF ( HASONEVALUE ( Clients[Code] ), FillEmpty ,BLANK() ))), [_1])

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: how to sum fo the prepared measure in the total

mic_rys — Tue, 01 Feb 2022 09:08:28 GMT

amitchandak
It's live connection so it not easy to simplify the data but I can show part of the model which is used.

The result of your code is:

Re: how to sum fo the prepared measure in the total

Anonymous — Wed, 09 Feb 2022 05:40:25 GMT

Hi mic_rys,

According to the description, it seems like a common measure total level calculation issues.

If that is the case, I'd like to suggest you refer to the following link to know how to handle this scenario.

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng

Re: how to sum fo the prepared measure in the total

mic_rys — Mon, 14 Feb 2022 12:53:47 GMT

HI, thanks, it helped somewhat! I added extra measure and now it summarizes well!

Last value by customer with filled values =

var _wynikGrand = sumx(

GROUPBY( Tasks, Clients[Code],'Date'[YearMonth] )

, [Filled values]

)

return

_wynikGrand