cancel
Showing results for 
Search instead for 
Did you mean: 
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
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors