Forum Discussion

Domenico96's avatar
Domenico96
Helper I
2 months ago
Solved

Total % not showing correctly

Hi everyone, I have an issue with a measure.

The desired output is to show the normal value for each month (the one defined in the RETURN), while for the current month—when no date is selected—I would like to display the closing estimate (KPI_Stima).

With the current measure, if I put the month in the dimension, the values are displayed correctly. However, the total percentage variation is not correct: instead, it returns the percentage of the current month when no Calendar date is selected (in this case, June).

What I would like is to keep the monthly calculation as it is, but have the total percentage calculated correctly.

Could you please give me some advice ? 

In this case, all the % are correct, but the total one is not. Instead of showing 55,1% ( the same value of june ) I should see something like ≈−34,1%

This is the DAX : 

Switch vs PY % =

VAR Oggi = TODAY()

RETURN

   
    VAR TipologiaSel = SELECTEDVALUE('Voci TIPO DAT'[Valori])
    VAR MaxData = CALCULATE(MAX('Calendar'[Date]))
   
    VAR NessunFiltroData = NOT ISFILTERED('Calendar'[Date])

    VAR MeseCorrente =
        YEAR(MaxData) = YEAR(Oggi) &&
        MONTH(MaxData) = MONTH(Oggi)

    VAR Voce = SELECTEDVALUE('Voci TIPO DAT'[Valori])
    VAR Tipo = SELECTEDVALUE('Tabella KPI Riepilogo'[Voce])

    VAR IsFatturato =
        CONTAINS(
            VALUES('Tabella Tipologia'[Tipo Dato]),
            'Tabella Tipologia'[Tipo Dato], "Fatturato"
        )

    VAR KPI_Stima =
        SWITCH(
            TRUE(),
            Voce = "KG" && Tipo = "Δ vs PY" && IsFatturato, DIVIDE( [Stima Chiusura Fatturato KG]-[Stima Chiusura Fatturato KG PY],[Stima Chiusura Fatturato KG PY]),
            Voce = "PZ" && Tipo = "Δ vs PY" && IsFatturato, DIVIDE( [Stima Chiusura Fatturato PZ]-[Stima Chiusura Fatturato PZ PY],[Stima Chiusura Fatturato PZ PY]),
            Voce = "QL" && Tipo = "Δ vs PY" && IsFatturato, DIVIDE( [Stima Chiusura Fatturato QL]-[Stima Chiusura Fatturato QL PY],[Stima Chiusura Fatturato QL PY]),
            Voce = "NR" && Tipo = "Δ vs PY" && IsFatturato, DIVIDE( [Stima Chiusura Fatturato NR]-[Stima Chiusura Fatturato NR PY],[Stima Chiusura Fatturato NR PY]),
            Voce = "€" && Tipo = "Δ vs PY" && IsFatturato, DIVIDE( [Stima Chiusura Fatturato €]-[Stima Chiusura Fatturato € PY],[Stima Chiusura Fatturato € PY])
        )

    RETURN
        IF(
            NessunFiltroData && MeseCorrente,
            KPI_Stima,
            SWITCH(
                TRUE(),
                TipologiaSel = "€", [Fatturato € vs PY %],
                TipologiaSel = "KG", [Fatturato KG vs PY %],
                TipologiaSel = "NR", [Fatturato NR vs PY %],
                TipologiaSel = "PZ", [Fatturato PZ vs PY %],
                TipologiaSel = "QL", [Fatturato QL vs PY %]
            )
        )

 


   

 




Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Shai_Karmani's avatar
    Shai_Karmani
    2 months ago

    Hey
    Your total row isn't a month, but MeseCorrente still fires there because MaxData grabs June, so the whole total drops into the KPI_Stima branch and copies June's 55,1%.

    Fix is simple. A % can't be summed across rows, so at the total you gotta rebuild it from the raw numbers, not reuse one month's value.

    Quick patch, just kill the estimate branch at the total:

    VAR IsTotale = NOT HASONEVALUE('Calendar'[Mese])
RETURN
IF(
    NessunFiltroData && MeseCorrente && NOT IsTotale,
    KPI_Stima,
    SWITCH( ... your normal vs PY % ... )
)

    That gets the leak out, but now the total ignores the estimate completely.

    If you want the total to actually count the estimate for June plus actuals for the other months, sum numerator and denominator separately, then divide once at the end:

    VAR IsTotale = NOT HASONEVALUE('Calendar'[Mese])
RETURN
IF(
    IsTotale,
    DIVIDE( total_current_or_estimate - total_PY, total_PY ),
    IF( NessunFiltroData && MeseCorrente, KPI_Stima, [normal vs PY %] )
)

     

    If it helped please mark as resolved & give a kudo.


    Shai Karmani | Data, Analytics & AI

    Let’s connect on LinkedIn  | Work with me | Get Updates on the hottest  Fabric & Power BI news



3 Replies

  • Shai_Karmani's avatar
    Shai_Karmani
    Super User
    2 months ago

    Hey
    Your total row isn't a month, but MeseCorrente still fires there because MaxData grabs June, so the whole total drops into the KPI_Stima branch and copies June's 55,1%.

    Fix is simple. A % can't be summed across rows, so at the total you gotta rebuild it from the raw numbers, not reuse one month's value.

    Quick patch, just kill the estimate branch at the total:

    VAR IsTotale = NOT HASONEVALUE('Calendar'[Mese])
RETURN
IF(
    NessunFiltroData && MeseCorrente && NOT IsTotale,
    KPI_Stima,
    SWITCH( ... your normal vs PY % ... )
)

    That gets the leak out, but now the total ignores the estimate completely.

    If you want the total to actually count the estimate for June plus actuals for the other months, sum numerator and denominator separately, then divide once at the end:

    VAR IsTotale = NOT HASONEVALUE('Calendar'[Mese])
RETURN
IF(
    IsTotale,
    DIVIDE( total_current_or_estimate - total_PY, total_PY ),
    IF( NessunFiltroData && MeseCorrente, KPI_Stima, [normal vs PY %] )
)

     

    If it helped please mark as resolved & give a kudo.


    Shai Karmani | Data, Analytics & AI

    Let’s connect on LinkedIn  | Work with me | Get Updates on the hottest  Fabric & Power BI news



  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    2 months ago

    Hi Domenico96 ,

    Thanks for reaching out to Microsoft Fabric Community.

    Just wanted to check if the response provided by Shai_Karmani was helpful. If further assistance is needed, please reach out.


    Thank you.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    2 months ago

    Hi Domenico96 ,

    We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
    Thank you.