Error calculating Tool tip with multiple filters. dax

[Syndicate_Admin]

good.

I have a problem with the table that attached, this table is calculated after selecting from 5 filters(2 of year, 2 of locality, 1 of indicator), so here everything correct is calculated, the problem I have with the tool tip, this carries an information that deepens about the indicator, each indicator is the sum of several activities and I want to show it disaggregated by activities as seen in the image.

Total1 is a summation and total2 is calculated as follows:

Total2 =
CALCULATE (
SUM (BI_CM[VALUE]),
FILTER (
ALL ( BI_CM ),
BI_CM[INDICATOR] = SELECTEDVALUE ( INDICATOR[INDICATOR] ) && BI_CM[CENTRO] = SELECTEDVALUE ( CENTERS[CENTER] ) && BI_CM[ANNO] = SELECTEDVALUE ( FECHA2[ANNO] )
&& BI_CM [MES] IN DISTINCT ( BI_CM[MES])
)
)

In the distinct I understand that the activity would now go, but it does not calculate me correctly, I can not find the formula. Any ideas?

Thank you.

[MFelix]

Hi @Aguirre ,

Based on the image is difficult to understand what is happening, however I see that you are using a filter on the table BI_CM, with an ALL syntax this makes that the values are being used are all the values in the table so the context even when with different granulatrity will pick up everything in the table try one of the following codes:

Total2 =
CALCULATE (
    SUM ( BI_CM[VALUE] ),
    FILTER (
        ALLSELECTED ( BI_CM ),
        BI_CM[INDICATOR] = SELECTEDVALUE ( INDICATOR[INDICATOR] )
            && BI_CM[CENTRO] = SELECTEDVALUE ( CENTERS[CENTER] )
            && BI_CM[ANNO] = SELECTEDVALUE ( FECHA2[ANNO] )
            && BI_CM[MES] IN DISTINCT ( BI_CM[MES] )
    )
)

Total2 =
CALCULATE (
    SUM ( BI_CM[VALUE] ),
    FILTER (
        ALL ( BI_CM ),
        BI_CM[INDICATOR] = SELECTEDVALUE ( INDICATOR[INDICATOR] )
            && BI_CM[CENTRO] = SELECTEDVALUE ( CENTERS[CENTER] )
            && BI_CM[ANNO] = SELECTEDVALUE ( FECHA2[ANNO] )
            && BI_CM[ACTIVIDAD]
                IN VALUES ( BI_CM[ACTIVIDAD] )
                    && BI_CM[MES] IN DISTINCT ( BI_CM[MES] )
    )
)

If this does not work can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Aguirre]

Hello, I have created an example for you because I cannot share the original, as you can see in the screenshot, I use several filters and the dashboard works well for me, the problem is the tool tips, that the second calculation does not show the correct thing.

Thank you very much for the help.

PBIX prueba 

[MFelix · Accepted solution]

Hi @Aguirre ,

Are you sure your calculation is correct in terms of the TotalY, asking this because of the monthy values.

What I did was to create a inactive relationship between year table and the Hoja1 then added the following measure:

Total_Y = 
CALCULATE(SUM(Hoja1[Valor]), USERELATIONSHIP('Year table'[Year],Hoja1[Year]), ALL(Hoja1[Year]),KEEPFILTERS( Hoja1[Mes] in DISTINCT((Mes[Mes]))))

Has you can see below I have results for the values you want however I have in more rows than you:

If I filter the Hoja 1 by the selections you have except the month you can see all the values are within the table, are you sure your TotalY is correctly calculated?