Calculate percentage of revenue based on year and month from hierarchy picker with multichoice

[dkarl403]

I want to calculate the percentage of revenue for the picked year/years AND month/months that the user have picked, My DAX formula is the following:

Revenue = IF (
    SELECTEDVALUE ('Month'[Month]) > 1;
    CALCULATE (
    SUM ( MFE_RR_Generell_VerTrans[Revenue] );
        FILTER ( ALL ( MFE_RR_Generell_VerTrans ); MFE_RR_Generell_VerTrans[RRKontoGruppID] = 101 && MFE_RR_Generell_VerTrans[DateMonth] = 'Month' && MFE_RR_Generell_VerTrans[DateYear] = SELECTEDVALUE ('Year'[Year]))
)
)

I know that this solution is not the correct one, but I dont know what else to try, this one (for obvious reasons) only works when you have picked ONE year AND one month. When i select multiple months the revenue wont calculate at all.

[v-shex-msft]

HI @dkarl403,

AFAIK, selectedvalue function not support deal with multiple selection(it will return blank if select multiple items), please use allselected or values with countrows function to instead.

Sample:

Revenue =
IF (
    COUNTROWS ( ALLSELECTED ( 'Month'[Month] ) ) > 1;
    CALCULATE (
        SUM ( MFE_RR_Generell_VerTrans[Revenue] );
        FILTER (
            ALL ( MFE_RR_Generell_VerTrans );
            MFE_RR_Generell_VerTrans[RRKontoGruppID] = 101
                && MFE_RR_Generell_VerTrans[DateMonth] IN ALLSELECTED ( 'Month'[Month] )
                && MFE_RR_Generell_VerTrans[DateYear] IN ALLSELECTED ( 'Year'[Year] )
        )
    )
)

Regards,

Xiaoxin Sheng

[dkarl403 · Accepted solution]

I had to modify my solution in order to get it to work even if none where selected, know I could have used ALLEXCEPT, but this works fine. Here is the full code:

OmsättningMFEUtfall = IF(COUNTROWS ( ALLSELECTED ( 'År'[År] )) = 8; CALCULATE (

    SUM ( MFE_RR_Generell_VerTrans[Utfall] );

            FILTER ( ALL ( MFE_RR_Generell_VerTrans ); MFE_RR_Generell_VerTrans[RRKontoGruppID] = 101 )

); CALCULATE (

    SUM ( MFE_RR_Generell_VerTrans[Utfall] );

            FILTER (ALLSELECTED ( MFE_RR_Generell_VerTrans ); MFE_RR_Generell_VerTrans[DatumAr] IN ALLSELECTED ( 'År'[År] ) && MFE_RR_Generell_VerTrans[RRKontoGruppID] = 101)

))