cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## DAX Formula

Hi everyone,

I created a DAX formula which returns a flag « X » on certain rows.

Now I would like to calculate the sum of the rows that have the flag by year. I created this formula but it doesn't work :

Total Sales with Flag = CALCULATE([Total Sales], FILTER(Sales, [Flag] = "X"))

Here the link of PBI file :

3 ACCEPTED SOLUTIONS
Super User

Hi PBI_37,

I was able to return the correct value, if I understood your intention.

``````Total Sales with Flag =
VAR YearMonth =
SUMMARIZE (
'Calendar',
'Calendar'[Year], 'Calendar'[Month Name]
)
VAR SalesbyMonth =
YearMonth,
"@Flag", [Flag]
)
VAR FilteredYearMonth =
FILTER (
SalesbyMonth,
[@Flag] = "X"
)
VAR Result =
SUMX (
FilteredYearMonth,
[Total Sales]
)

RETURN Result``````

The reason mine works and yours doesn't is because when you put your flag in the filter condition in your measure, it is looking at every sales line and checking if the sales on that order are greater than 4,000. Mine forces it to make the check at the year/month level (like you have in your matrix).

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

Proud to be a Super User!

Super User

Assez facile,

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Super User

Hi,

This measure works

``Total Sales with Flag = CALCULATE([Total Sales],FILTER(VALUES('Calendar'[Month Name]),[Total Sales]>4000))``

Regards,
Ashish Mathur
http://www.ashishmathur.com
9 REPLIES 9
Helper I

Thank you @Ashish_Mathur , @ThxAlot@Wilson_

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Thanks for the update, happy to help! 😄

Proud to be a Super User!

Super User

Hi,

This measure works

``Total Sales with Flag = CALCULATE([Total Sales],FILTER(VALUES('Calendar'[Month Name]),[Total Sales]>4000))``

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Assez facile,

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Super User

Hi PBI_37,

I was able to return the correct value, if I understood your intention.

``````Total Sales with Flag =
VAR YearMonth =
SUMMARIZE (
'Calendar',
'Calendar'[Year], 'Calendar'[Month Name]
)
VAR SalesbyMonth =
YearMonth,
"@Flag", [Flag]
)
VAR FilteredYearMonth =
FILTER (
SalesbyMonth,
[@Flag] = "X"
)
VAR Result =
SUMX (
FilteredYearMonth,
[Total Sales]
)

RETURN Result``````

The reason mine works and yours doesn't is because when you put your flag in the filter condition in your measure, it is looking at every sales line and checking if the sales on that order are greater than 4,000. Mine forces it to make the check at the year/month level (like you have in your matrix).

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

Proud to be a Super User!

Super User

Total Sales with Flag = CALCULATE(SUM('Sales'[Total Sales]), FILTER(Sales, 'Sales'[Flag] = "X"))

Helper I

Thank you @mickey64 but it doesn't work

Super User

Total Sales with Flag = CALCULATE([Total Sales], FILTER(SUMMARIZE(Sales,Sales[Year],Sales[Month Name],Sales[Total Sales],Sales[Flag]), [Flag] = "X"))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.