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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors