Measure error caused by calculated column

Question

See additional information added and link to .pbix file in later post.&nbsp;I am having problems with a measure and calculated column that seem to be interacting with one another in unexpected ways, producing inaccurate results. I have never seen this type of behaviour before and am unsure what is causing it.&nbsp;The measure calculates percentage market share by institution and looks like this:&nbsp;&nbsp;Person share by institution =&nbsp;DIVIDE([Person count], CALCULATE([Person count], ALL(MarketData[Institution], MarketData[Institution (groups)], MarketData[Division])), BLANK())NOTE: "Person count" is a simple sum of a numeric column.&nbsp;&nbsp;I am using this measure in a matrix table with Institution in rows and fiscal year in columns. The fiscal year column is a calculated column based on the following:&nbsp;Fiscal Yr =var CurrentCycle = MarketData[Cycle]var CurrentTerm = MarketData[TermNumber]var RevisedYear = IF(CurrentTerm=3, CurrentCycle+1, CurrentCycle)var FollowingYear = RIGHT(FORMAT(RevisedYear+1, "General Number"), 2)returnFORMAT(RevisedYear, "General Number")&amp;"-"&amp;FollowingYear&nbsp;Everything seems to work fine initially. However, if I do two things - 1) filter out one or more fiscal year values, and 2) include a filter on another column and select more than one filter value - the percentage calculation acts strangely - see below:&nbsp;&nbsp;&nbsp;&nbsp;Note the odd percentages for the individual rows that add up to more than 100%. It ought to look like this:&nbsp;&nbsp;The only change I made here was to swap out the Fiscal Year calculated column that I created using DAX for an identical column that I created using Power Query. The fiscal year values themselves are exactly the same.&nbsp;&nbsp;Can anyone help me figure out what exactly is going on here? I have never seen anything like this before and could use some guidance.&nbsp;&nbsp;Thanks!&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

xifeng_l · Answer

Hi&nbsp;GavinH&nbsp;,&nbsp;That's interesting. I need more info to locate the problem, can you provide the pbix file?

gavinh · Answer

Unfortunately, I do not appear to have the ability to attach a file in this forum. Not sure why that would be.&nbsp;

anonymous · Answer

Hi GavinH&nbsp;,
&nbsp;
Thanks for the reply from xifeng_L&nbsp;.
&nbsp;
As he said, screenshots and syntax alone can't help you much.
&nbsp;
You can upload the pbix file to a website like OneDrive, Google Drive, Dropbox, and then share the URL.
&nbsp;
Remember not to log in to your own account in Power BI Desktop when uploading the pbix file.
&nbsp;
If you have any other questions, please feel free to contact me.
&nbsp;
Best Regards,YangCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;to help the other members find it more quickly.If I misunderstand your needs or you still have problems on it, please feel free to let us know.&nbsp;Thanks a lot!

gavinh · Answer

I have uploaded the .pbix file and the source Excel file to a Github repository here:&nbsp;https://github.com/ghammel/PBIBug/blob/main/README.md&nbsp;&nbsp;And here is a restatement of the problem I am facing -&nbsp;I have encountered a problem in Power BI with using a calculated column [Fiscal Year] in combination with all of the following conditions:A measure that uses the ALL() function - both [Company share] and [Denominator] do this.A filter on the [Fiscal Year] column that filters out one or more values. (I filtered out values 2018-19 and 2019-20 for illustration.)A filter on another column - I used [Region] - that selects more than one, but not all values. (I selected values 111 and 113 for illustration.)The problem appears to be that the [Region] filter is interacting in an odd way with the measures. For each value of [Company], it is only calculating a grand total denominator only on the basis of the [Region] values that are present for that particular [Company] in that particular [Fiscal Year]. For example, in the 2020-21 Fiscal Year, companies C, F, and I have values for Region 113 but do not have values from Region 111. When both Regions are selected in the filter, the grand total calculation for these companies - [Denominator] - is only drawing from the rows with Region 113, which results in incorrect percentage [Company share] calculations.&nbsp;&nbsp;&nbsp;Despite these obvious errors, there seems to be no problem with the [Denominator] and [Company share] measures themselves. In fact, when I use an alternate Fiscal Year column created in Power Query rather than DAX (entitled [FiscalYrQuery]), the measures act as expected and calculate the grand total denominator properly.&nbsp;&nbsp;&nbsp;If anyone else has encountered this issue, I would appreciate hearing about it. Also, if anyone knows how to contact the Product Development or Quality Review teams, that would be helpful. I have spent weeks back-and-forth with Power BI Customer Support with nothing to show for it.&nbsp;(PLEASE NOTE: I am aware that there are other ways to fix this issue for this particular instance - not using a DAX calculated column, for example. My concern here is more general - that this is a core functionality that should work properly in Power BI but does not appear to do so.)

Forum Discussion

Measure error caused by calculated column

4 Replies

Related content

DAX Calculated Measure

DAX Measure for Ranking the valid rows and calculating the average rating

Measure or Calculated Column

Error causing pipeline failure

Modelling issue causing measure not properly working