cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stemar_Aubert
Resolver I
Resolver I

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

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 :

 

DATETYPECOUNTRYUNIT
01/01/2021AUSA1
01/01/2021BFR1
01/02/2021CUK1

 

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 :

 

USA33.3%
FR33.3%
UK33.3%
TOTAL100.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:

 

CountryMetric 1Metric 2

USA

33.3%5
FR33.3%10
UK33.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

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Stemar_Aubert  ,

I created the data:

v-yangliu-msft_0-1622790355099.png

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:

v-yangliu-msft_1-1622790355101.png

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:

photo1.png

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.

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

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
Community Support
Community Support

Hi  @Stemar_Aubert  ,

I created the data:

v-yangliu-msft_0-1622790355099.png

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:

v-yangliu-msft_1-1622790355101.png

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:

photo1.png

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.

View solution in original post

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:

 
2021-06-10 09-09-27_Start.png
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
Solution Sage
Solution Sage

Try isfiltered rather than hasonevalue?

Hi,

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.