Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ecoflux38170
Regular Visitor

Total sum of showed values

Hello everyone,

 

I created a measure allowing to subtract indexes between 2 dates, but the total displayed is not the correct one, and I would like to have the total of the values ​​displayed in my matrix.

Here are some screenshots to show you how it works.

 

Table Index kilométrique : 

Capture d’écran 2023-03-24 083529.png

 

 

Measure 1 : Index kilométrique(km) = SUM('Index Kilométrique'[Index kilométrique])

 

Measure 2: 

Kilométrage =

IF(
    ISFILTERED('Chronologie'[Chronologie]),
    ERROR("Les mesures rapides de Time Intelligence peuvent être regroupées ou filtrées seulement par la hiérarchie de dates ou les colonnes de dates principales fournies par Power BI."),
    VAR __PREV_YEAR = CALCULATE([Index kilométrique(km)], DATEADD('Chronologie'[Chronologie].[Date], -1, YEAR))
    VAR __CURRENT_YEAR = ('Index Kilométrique'[Index kilométrique(km)])
    VAR __CALCUL = (__CURRENT_YEAR - __PREV_YEAR)
    VAR __RESULTAT = IF(__CALCUL < 0, BLANK(), __CALCUL)
    RETURN
        __RESULTAT
)

 

Matrice :

Capture d’écran 2023-03-24 083704.png

 

I would like finally to make a graph with annual kms for my fleet.

 

Thanks for your help !

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

Hi @ecoflux38170 

Please try

Kilométrage =
IF (
    ISFILTERED ( 'Chronologie'[Chronologie] ),
    ERROR ( "Les mesures rapides de Time Intelligence peuvent être regroupées ou filtrées seulement par la hiérarchie de dates ou les colonnes de dates principales fournies par Power BI." ),
    SUMX (
        VALUES ( 'Table'[Libellé] ),
        VAR __PREV_YEAR =
            CALCULATE (
                [Index kilométrique(km)],
                DATEADD ( 'Chronologie'[Chronologie].[Date], -1, YEAR )
            )
        VAR __CURRENT_YEAR = [Index kilométrique(km)]
        VAR __CALCUL = __CURRENT_YEAR - __PREV_YEAR
        VAR __RESULTAT =
            IF ( __CALCUL < 0, BLANK (), __CALCUL )
        RETURN
            __RESULTAT
    )
)

View solution in original post

Hi @ecoflux38170 
Not Sure if I fully understand. However, please try

Kilométrage =
IF (
    ISFILTERED ( 'Chronologie'[Chronologie] ),
    ERROR ( "Les mesures rapides de Time Intelligence peuvent être regroupées ou filtrées seulement par la hiérarchie de dates ou les colonnes de dates principales fournies par Power BI." ),
    SUMX (
        VALUES ( 'Table'[Libellé] ),
        VAR __NEXT_YEAR =
            CALCULATE (
                [Index kilométrique(km)],
                DATEADD ( 'Chronologie'[Chronologie].[Date], 1, YEAR )
            )
        VAR __CURRENT_YEAR = [Index kilométrique(km)]
        VAR __CALCUL = __NEXT_YEAR - __CURRENT_YEAR
        VAR __RESULTAT =
            IF ( __CALCUL < 0, BLANK (), __CALCUL )
        RETURN
            __RESULTAT
    )
)

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

Hi @ecoflux38170 

Please try

Kilométrage =
IF (
    ISFILTERED ( 'Chronologie'[Chronologie] ),
    ERROR ( "Les mesures rapides de Time Intelligence peuvent être regroupées ou filtrées seulement par la hiérarchie de dates ou les colonnes de dates principales fournies par Power BI." ),
    SUMX (
        VALUES ( 'Table'[Libellé] ),
        VAR __PREV_YEAR =
            CALCULATE (
                [Index kilométrique(km)],
                DATEADD ( 'Chronologie'[Chronologie].[Date], -1, YEAR )
            )
        VAR __CURRENT_YEAR = [Index kilométrique(km)]
        VAR __CALCUL = __CURRENT_YEAR - __PREV_YEAR
        VAR __RESULTAT =
            IF ( __CALCUL < 0, BLANK (), __CALCUL )
        RETURN
            __RESULTAT
    )
)

@tamerj1 I've another question for you.

 

The measurement is correct, but the years are off (see image below)

ecoflux38170_1-1679660893648.png

 

 

Here are the readings used:

ecoflux38170_2-1679660917806.png

 


This means that :
For a statement made in 2017, the mileage corresponds to 2016; however, on the matrix, it is displayed in 2017.


Is there a solution to work around this problem?

 

Thanks for your help !

Hi @ecoflux38170 
Not Sure if I fully understand. However, please try

Kilométrage =
IF (
    ISFILTERED ( 'Chronologie'[Chronologie] ),
    ERROR ( "Les mesures rapides de Time Intelligence peuvent être regroupées ou filtrées seulement par la hiérarchie de dates ou les colonnes de dates principales fournies par Power BI." ),
    SUMX (
        VALUES ( 'Table'[Libellé] ),
        VAR __NEXT_YEAR =
            CALCULATE (
                [Index kilométrique(km)],
                DATEADD ( 'Chronologie'[Chronologie].[Date], 1, YEAR )
            )
        VAR __CURRENT_YEAR = [Index kilométrique(km)]
        VAR __CALCUL = __NEXT_YEAR - __CURRENT_YEAR
        VAR __RESULTAT =
            IF ( __CALCUL < 0, BLANK (), __CALCUL )
        RETURN
            __RESULTAT
    )
)

@tamerj1 I want exactly this ! Thank you so much !

@tamerj1 thanks a lot, it works !

ecoflux38170_0-1679646578391.pngecoflux38170_1-1679646592686.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.