Unfiltered column subtotal

[nb25064]

Hi everyone!

I'm trying to create a matrix that displays some measures filtered by a column and then, in the row subtotal I would like to have the unfiltered total for those measures. Is it possible?

As you can see, I've some measures selected and a filtered column, but I'm struggling to find a way of showing this information as if it was a simple "Participant A vs ALL Participants".

I've already created those measures using the ALL function, to get the values that I want, but i'm still failing to show those measures in a visual that is easy to compare (like the above screenshot would be).

If unfiltering the subtotals is not possible, is there a workaround for this?

Thanks,

Lucas

[v-alq-msft · Accepted solution]

Hi, @nb25064 

Based on your description, I created data to reproduce your scenario.

Table:

You may create measures as below.

Abatido ao activo = 
IF(
    ISINSCOPE('Table'[Participant]),
    SUM('Table'[Value3]),
    CALCULATE(
        SUM('Table'[Value3]),
        ALL('Table')
    )
)
Capital em Divide = 
IF(
    ISINSCOPE('Table'[Participant]),
    SUM('Table'[Value1]),
    CALCULATE(
        SUM('Table'[Value1]),
        ALL('Table'[Participant])
    ) 
)
Rácio de Incumprimento = 
IF(
    ISINSCOPE('Table'[Participant]),
    SUM('Table'[principal_arrears_amount])/SUM('Table'[principal_amount]),
    CALCULATE(
        SUM('Table'[principal_arrears_amount])/SUM('Table'[principal_amount]),
        ALL('Table')
    )
)
Vencido = 
IF(
    ISINSCOPE('Table'[Participant]),
    SUM('Table'[Value2]),
    CALCULATE(
        SUM('Table'[Value2]),
        ALL('Table')
    )
)

Result:

Best Regards

Allan

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

[nb25064]

 Thanks to all, for all your help and replies! @v-alq-msft solution did the trick. 

[amitchandak]

@nb25064 ,

Refer if this can help

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

like

IF(ISFILTERED(Table[Type]),Sum(Table[Value]),calculate(Sum(Table[Value]),all(Table)))

IF(HASONEVALUE(Table[Type]),Sum(Table[Value]),calculate(Sum(Table[Value]),all(Table)))

[AllisonKennedy]

Hi Lucas~

Can you explain further what you are trying to acheive? Is the screenshot you shared the desired result or are you looking for the totals column to reflect something different?

@nb25064 Also, can you share the DAX for your measures that you're currently using?

[nb25064]

About the measures:

Rácio de Incumprimento = SUM(fact_contracts[principal_arrears_amount])/Sum(fact_contracts[principal_amount])

Normal = CALCULATE(TOTALYTD(SUM(fact_contracts[original_principal_amount]);'Dim_Date'[reference_dt]);dim_arrears_status[arrears_status_cd] ="000")

The other ones are just sums.

What I'm trying to achieve is a way of showing how a participant is performing, comparing those measures (filtered for Participant A in this case) with the same measures but unfiltered(Total column). In the screenshot, the totals column is equal to Participant A, and that happens because were filtering for that Participant(Participant A), but I would like to have the sum of all participants for each measure.

I forgot to mention, those measures are appearing as rows because I've enabled the "show on rows" option.

[AllisonKennedy]

@nb25064 Thanks for the clarification. 

Try @amitchandak 's suggestion of redefining the measure to include an IF check to see if the value is a single filter or subtotal, and if subtotal to ignore the filters:

Rácio de Incumprimento = IF(HasOneValue([participant]), SUM(fact_contracts[principal_arrears_amount])/Sum(fact_contracts[principal_amount]), calculate(SUM(fact_contracts[principal_arrears_amount])/Sum(fact_contracts[principal_amount]), ALL([participant]))

Or if you want to split into 2 measures:

Keep your existing measure:

Normal = CALCULATE(TOTALYTD(SUM(fact_contracts[original_principal_amount]);'Dim_Date'[reference_dt]);dim_arrears_status[arrears_status_cd] ="000")

Then Create New Measure:

Normal Total = IF(HasOneValue([Participant]), [Normal], CALCULATE([Normal], ALL([Participant])))

I don't know the name of the Table[Column] which holds the participant information so you'll need to replace that, and also fix syntax to match your region, but use those new measures in the table and you should get the results you're looking for. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv