topic Re: Formula DAX: Sum up the values in a month when nothing is chosen in the slicer in DAX Commands and Tips

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

Anonymous — Wed, 08 May 2024 13:49:46 GMT

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.]

)

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

tamerj1 — Wed, 08 May 2024 14:01:57 GMT

Hi Anonymous
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.] )

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

yyzheng12 — Wed, 08 May 2024 14:35:01 GMT

You can also use HASONEVALUE dax

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

tamerj1 — Wed, 08 May 2024 15:42:31 GMT

yyzheng12

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

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

Anonymous — Thu, 09 May 2024 02:17:52 GMT

Hi Anonymous ,

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.

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

Anonymous — Mon, 13 May 2024 09:21:33 GMT

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)

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

tamerj1 — Mon, 13 May 2024 13:58:22 GMT

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

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

Anonymous — Mon, 13 May 2024 14:04:18 GMT

Hi Anonymous

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