Avoid totals or Grand Totals in using Measure but not by visual format options

[Anonymous]

Hello All,

I have a measure, which is based on some calculations and i am using that measure in several visuals in several pages of my report.

Now my client came with the requirement such as, for a perticular visual assume it as Matrix, when ever i include a type in Rows and measure which is "[Amt]" in values, it should not give me the Grand Total valus by default and when i dont include the Type in Rows, then it should give me the total values.

Please check the below image.

In above image,In first matrix visual from left(Amt with Including Type), by using ISINSCOPE function im able to restrict the grand totals or totals. 

But in Amt without including type, when i remove the Type it giving me blank which is obvious.

What i would like to have is, need to have total value when i dont include type but when i include type it should give me values based on the types but not the Grand total or Total.

I know we can do this by turning off Visual Format Grand total or Totals or Subtotals option. But i would like to know Is it possible with DAX?.

Any help or suggestions please.

Thanks,

Mohan V.

[mohammedadnant]

Hi @Anonymous 

Good day,

check this image, will help, use HASONEVALUE DAX.

Pls hit like button,

Thanks & Regards,

Mohammed Adnan

https://www.youtube.com/taik18

[Anonymous]

@mohammedadnant  thanks for the repl. Much appreciated.

I did tried this, but its working when i include type column only.

But once i remove type column, then it gives me nothing but blank as below.

But what i am looking for is, when i include type column it should avoid Grand totals but when i dont include it should give me the total value which is in above case it is $75787936733.97992.

Please suggest.

Thanks,

Mohan V.

[mohammedadnant]

@Anonymous ,

I suggest you to create 2 different measure for this.

Thanks & Regards,

Mohammed Adnan

https://www.youtube.com/taik18

[Anonymous]

Thats where i was struck @mohammedadnant .

I even tried that as well.

Data:-

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY29DoIwEIBfpelMml57lHb2EYwTYbhATS45qmIdeHv/FgZ0/376Xh+EC48ksqo7zyy0qCXTNHPVjU42pLbz3jg9NP9YVS+KyqrONLJwXV8u+JAAkkOTPvIx3x65VCbZDBwGtBEBfyA7XR89tthaNF/pVK5CpeRpm4UYO8ToTdhn9rroHAB29t0dng==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Readmit Type" = _t, Amt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Readmit Type", type text}, {"Amt", type number}})
in
    #"Changed Type"

two measure,

_Amt = SUM(data[Amt])

_Amt Avoiding Totals = IF(ISINSCOPE(data[Readmit Type]),SUM(data[Amt]))

Now the output is 

_Amt Avoiding Totals_2 = IF(ISINSCOPE(data[Readmit Type]),SUM(data[Amt]),[_Amt])

I am really confused here how to make that condition to get the total value when there is no type.

[Anonymous]

@mohammedadnant @amitchandak any help please.

Thanks,

Mohan V.

[mohammedadnant · Accepted solution]

Hi @Anonymous ,

I suggest you to use 2 measure, where ever u need without type, u use that measure.

if u need with type use  another measure.

don't try to mixed both in 1, it will not work.

use table for with type data, use card for without type data.

hope this will help.

Thanks & Regards,

Mohammed Adnan

https://www.youtube.com/taik18

[amitchandak]

@Anonymous , check isfiltered can help. if isfiltered is false. Use both isfiltered  and isinscope and try.

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

[Anonymous]

@amitchandak Thanks for your reply.

I did tried ISINSCOPE and ISFILTERED individually but when i try both together its not working.

Can you please help me.

Thanks,

Mohan V.

[Anonymous]

@amitchandak  any suggestions please.

Thanks,

Mohan V.