- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations (Read-only)
- Power Platform and Dynamics 365 Integrations (Read-only)
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: DAX CALCULATE ERROR

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

DAX CALCULATE ERROR

06-15-2022
04:53 PM

Hello, I have the following problem.

I have a measure built in DAX called Share % SKU ACT Volume, it is the percentage participation that each SKU has in the total ACT Volume, with that measure I have no problem.

Now I need to build a SUMIF that calculates the Share % SKU ACT Volume grouped by category for which I am using CALCULATE. So far I managed to build a code that brings me the correct result but when I apply the region filter or any filter, the result is static.

my code is as follows:

`SUM of SKU ACT Volume Share % by Category Static = CALCULATE(Worksheet[SKU ACT Volume % Share], ALLEXCEPT(Worksheet, Worksheet[Month], Worksheet[Category]))`

I understand that the result is static because I am using ALLEXCEPT, so he tried to set my CALCULATE in such a way that my result changes and is correct according to the applied filters.

So I come up with the following code:

`SUM of Share % SKU ACT Volume by category = CALCULATE(Worksheet[Share % SKU ACT Volume], ALLSELECTED(Worksheet), VALUES(Worksheet[Month]), VALUES(Worksheet[Category]))`

The result I get is incorrect, my intention is to get to the result of the measure SUM of SKU ACT Volume Share % by Category Static but that the result changes according to the applied filters.

How can I achieve it?

Share PBI File PBI FILE

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2022
07:47 PM

I solved it like this:

```
VAR Numerator = CALCULATE(SUM(Worksheet[ACT Volume]), ALLSELECTED(Worksheet), VALUES(Worksheet[Month]),VALUES(Worksheet[Category]))
VAR Denominator = CALCULATE(SUM(Worksheet[ACT Volume]), ALLSELECTED(Worksheet))
RETURN
DIVIDE(Numerator, Denominator)
```

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2022
07:47 PM

I solved it like this:

```
VAR Numerator = CALCULATE(SUM(Worksheet[ACT Volume]), ALLSELECTED(Worksheet), VALUES(Worksheet[Month]),VALUES(Worksheet[Category]))
VAR Denominator = CALCULATE(SUM(Worksheet[ACT Volume]), ALLSELECTED(Worksheet))
RETURN
DIVIDE(Numerator, Denominator)
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2022
05:08 PM

you can't calculate a colnum, it need to be a measure, like sum the colnum or ave the colnum

`SUM of SKU ACT Volume Share % by Category Static = CALCULATE(Sum(Worksheet[SKU ACT Volume % Share]), ALLEXCEPT(Worksheet, Worksheet[Month], Worksheet[Category]))`

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2022
05:15 PM

A calculated column would contain static values and I need my result to change as I apply filters on the report.

To add the measure I must create another measure with a SUMX to force the context of the row but before doing so I must obtain the correct values from my calculate

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2022
06:01 PM

OKOK

I download you file.

So... you want calculate ACT Volume Share % for each SKU ID base on colnum Month and colnum Category. And sum all those number become 100%

like this

```
Measure = AVERAGEX(
VALUES(Worksheet[index]),
CALCULATE(
DIVIDE(
SUM([ACT Volume]),
CALCULATE(
SUM([ACT Volume]),
REMOVEFILTERS(Worksheet[SKU ID])
)
)
)
)
```

`index = [Month]&[Category]`

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2022
06:39 PM

Thanks for your answer. It is not what I need, the participation percentage is already calculated, which is the Share % SKU ACT Volume measure, now what I have to do is, that measure, which is my participation percentage, include it in a calculate that groups it by month and category .

So if you can see with the following code:

`SUM of Share % SKU ACT Volume by category Static = CALCULATE(Worksheet[Share % SKU ACT Volume], ALLEXCEPT(Worksheet, Worksheet[Month], Worksheet[Category]))`

Result

Without filters

It has the result that I expect, what happens is that this result is not dynamic since I apply the region filter and it is the same all the time, what I need is to get that result and that it fits to the granularity defined by the filters.

With filters

The expected result would be the distribution of 100% that represents the total of my participation among my four categories and that this be dynamic every time I change the region filter.

Announcements

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Check out the April 2024 Power BI update to learn about new features.

Top Solution Authors

User | Count |
---|---|

41 | |

21 | |

20 | |

14 | |

14 |

Top Kudoed Authors

User | Count |
---|---|

40 | |

39 | |

34 | |

19 | |

17 |