DAX question - Disable total calculation for a measure in a table.

[Stemar_Aubert]

Hello all,

I have come upon a recent challenge and thought I'd share here to get some insights.

Consider a standard table as per below :

DATE	TYPE	COUNTRY	UNIT
01/01/2021	A	USA	1
01/01/2021	B	FR	1
01/02/2021	C	UK	1

Tasked to calculate a ratio, I created a mesure that provides in %, the breakdown between each country. For exemple, results of my measure would be as such :

USA	33.3%
FR	33.3%
UK	33.3%
TOTAL	100.0%

Now, besides using a color trick to have the total white and thus invisible, how would you go to disable total calculation for this measure only, knowing that other measures still need to yield a total in the same table:

Country	Metric 1	Metric 2
USA	33.3%	5
FR	33.3%	10
UK	33.3%	12
TOTAL		27

Trying to use IF(HASONEVALUE() to wrap the measure doesn't work, as it needs to be a column. Trying to force a column such as the one were units are stored doesn't work, and still gives me a total.

For those interested, my measure is a Rolling 12 ratio and is quite lenghty:

Take Rate 1Y =
DIVIDE (
    CALCULATE (
        IF (
            COUNTROWS ( VALUES ( 'MARK ACT GRADE'[PRODUCTION DATE].[MonthNo] ) ) = 1,
            SUM ( 'MARK ACT GRADE'[SN_CNT] ),
            AVERAGEX (
                VALUES ( 'MARK ACT GRADE'[PRODUCTION DATE].[MonthNo] ),
                SUM ( 'MARK ACT GRADE'[SN_CNT] )
            )
        ),
        DATESINPERIOD (
            'MARK ACT GRADE'[PRODUCTION DATE],
            LASTDATE ( 'MARK ACT GRADE'[PRODUCTION DATE] ),
            -12,
            MONTH
        )
    ),
    CALCULATE (
        IF (
            COUNTROWS ( VALUES ( 'MARK ACT GRADE'[PRODUCTION DATE].[MonthNo] ) ) = 1,
            SUM ( 'MARK ACT GRADE'[SN_CNT] ),
            AVERAGEX (
                VALUES ( 'MARK ACT GRADE'[PRODUCTION DATE].[MonthNo] ),
                SUM ( 'MARK ACT GRADE'[SN_CNT] )
            )
        ),
        DATESINPERIOD (
            'MARK ACT GRADE'[PRODUCTION DATE],
            LASTDATE ( 'MARK ACT GRADE'[PRODUCTION DATE] ),
            -12,
            MONTH
        ),
        ALLSELECTED ( 'MARK ACT GRADE' )
    ),
    0
)

If somebody has an idea to wrap this in a statement that would allow to hide the total, I am happy to learn.

Thanks,

Stem

[v-yangliu-msft]

Hi  @Stemar_Aubert ,

Glad to hear that you have sloved problem, you can mark the correct answer, so that others can learn from it too?

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yangliu-msft · Accepted solution]

Hi  @Stemar_Aubert  ,

I created the data:

Create measure.

Metric1 =
var _1=COUNTAX(FILTER(ALL('Table'),'Table'[COUNTRY]=MAX('Table'[COUNTRY])),'Table'[COUNTRY])
var _2=COUNTROWS(ALL('Table'))
return
DIVIDE(_1,_2)

Metric2 =
CALCULATE(COUNT('Table'[COUNTRY]),FILTER(ALL('Table'),'Table'[DATE]=MAX('Table'[DATE])))

Form this data:

There are two ways Measure of Total were not displayed:

1. HASONEFILTER：

Create measure.

hasonefilter =
IF(
    HASONEFILTER('Table'[DATE]),
    IF([Metric2]<>BLANK(),'Table'[Metric2],BLANK()),BLANK())

2. HASONEVALUE

hasonevalue =
IF(HASONEVALUE('Table'[COUNTRY]),[Metric2],BLANK())

3. Result：

If the answer is not what you expected, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Stemar_Aubert]

Hi @v-yangliu-msft 

Your post has helped me reformulate the question in a better way I believe.

Consider the ContosoSales dataset and below matrix:

Creating a Matrix using the following Hierarchy:

ContinentName -> BrandName -> ClassName

And building 3 measures to evaluate ISFILTERED()

BrandNameCheck = ISFILTERED('Product'[BrandName])

ClassNameCheck = ISFILTERED('Product'[ClassName])

ContinentNameCheck = ISFILTERED('Geography'[ContinentName])

I found out that what I am looking for is a Boolean result where Grand Total yields the opposite of the rest of values and subtotals. This is achieved by applying ISFILTERED() to the highest level of the hierarchy.

Applying this to my own model has worked. Thanks for the insights and guidance!

[jthomson]

Try isfiltered rather than hasonevalue?

[Stemar_Aubert]

Hi,

Doesn't work either. I don't think any function using a column as argument would be effective here unfortunately.