Formula DAX: Sum up the values in a month when nothing is chosen in the slicer

[TattyPattyMatty]

Hey everyone,

I have come across a bottleneck and hope that I could obtain some suggestions here😊

I have a table containing a column showing cummulated sum of cost from each country in each month. I would like to, then, create a bar chart controlled by slicer, with:
Data in this column (cummulated sum value from each country in each month) as y-axis value
Month as x-axis value
Country as the value in slicer 

Meanwhile, I wish to fulfill such functions with the inserted slicer: 
When specific country is chosen,  the chart shows the cummulated sum of cost from this specific country in each month ; 
When no country is chosen, the chart shows the sum of the cummulated sum of cost from all countries in each month. 

The following is the DAX Formula that I wrote in order to achieve such goal. PowerBI detects no synthax error in the formula itself. However, when I put this measure in the visualization field, the y-axis keeps showing the cummulated sum of cost from each country, even if no country is chosen in the slicer when it should be showing the cummulated sum of cost from all countries in each month...It would be greatly appreciated if someone could tell me where my formula goes wrong, or the DAX Formula is not the root cause to this problem in the first place?   

Thanks a lot! 😁

The code I wrote for the measure:

Dynamic =

     IF(ISFILTERED(Table[Country]),

     CALCULATE(

            SUM(Table[CountryExpCummu.]),

            FILTER(Table,

                  Table[Month] <= EARLIER(Table[Month])

            )

     ),

        [CountryExpCummu.]

     )

[v-kaiyue-msft · Accepted solution]

Hi @TattyPattyMatty ,

I want to confirm the valuable inputs provided by @tamerj1 . Here i have another idea and i would like to share it for reference.

1. create measure to calculate cumulative sales for selected country.

Measure =
CALCULATE(SUM('financials'[ Sales]),FILTER(ALL('financials'),'financials'[Month Number] <= MAX('financials'[Month Number]) && 'financials'[Country] = SELECTEDVALUE('financials'[Country])))

2. create measure to show different results depending on whether the slicer has a selection or not.

Measure 2 =
VAR _all_sum = CALCULATE(SUM('financials'[ Sales]),FILTER(ALL('financials'),'financials'[Month Number] <= MAX('financials'[Month Number])))
RETURN
IF(ISFILTERED(financials[Country]),'financials'[Measure],_all_sum )

3. Use MEASURE 2 as the y-axis value.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TattyPattyMatty]

Hi @v-kaiyue-msft 

Thank you so much! I apply your method to my own context and it works perfectly.

[yyzheng12]

You can also use HASONEVALUE dax

[tamerj1]

@yyzheng12 

As long the column is part if the visual's filter context then it always HASONEVALUE 

[tamerj1]

Hi @TattyPattyMatty 
That is because 'Table'[Country] is part of the filter context of the visual, therefore it is always filtered.

Please try

Dynamic =
IF (
    COUNTROWS ( ALLSELECTED ( Table[Country] ) ) = 1,
    CALCULATE (
        SUM ( Table[CountryExpCummu.] ),
        FILTER ( Table, Table[Month] <= EARLIER ( Table[Month] ) )
    ),
    [CountryExpCummu.]
)

[TattyPattyMatty]

Hi @tamerj1 

Thank you so much for your advice!  I would like to, however, ask for your further advice since I am not sure whether COUNTROWS function would work under my situation.
I forgot to mention that each country has multiple rows that would all show the (same) cummulated cost in each specific month (the reason for that is that there could be multiple costs incurred in each country per month, so when I calculate the sum and then cummulate again those sum numbers in PowerBI without using INDEX to exclude the duplicate number, all the duplicate cummulated data would show)

[tamerj1]

@TattyPattyMatty 
DISTINCT, VALUES, ALL & ALLSELECTED all of them returns a distinct list of values - or rows if you wrap them around a mulitcolumn table -