cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

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

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.]
)
1 ACCEPTED SOLUTION
Community Support

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.

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.

7 REPLIES 7
Community Support

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.

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.

Frequent Visitor

Hi @v-kaiyue-msft

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

Helper I

You can also use HASONEVALUE dax

Super User

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

Super User

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

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

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)

Super User

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