Measure in graph returns inconsistent results when published to Service

[AKirk]

I have an issue where a graph that works fine in Desktop breaks when published to the service.

Here is what it looks like in the desktop app:

And here is what the same graph looks like when published to the service:

I cannot share the data directly, as they are sensitive, and I have been unable to replicate this with a sample stripped of the sensitive portions.

I use a summarized table to account for an object that may have more than one cause, given by:
SampleSum = SUMMARIZECOLUMNS('SampleData'[ID], 'SampleData'[Cause], 'SampleData'[site], 'SampleData'[RP], 'SampleData'[Type], 'SampleData'[Confirmed?], 'SampleData'[Sup], "intercause", DISTINCTCOUNT(('SampleData'[ID])))

Then I generate two measures (in a separate table), which are the columns and the line in the graph:
TotalCause = sumx(SampleSum, SampleSum[intercause])

causecum% = DIVIDE (
CALCULATE ([TotalCause],
FILTER (ALL ( 'SampleSum'[Cause] ),
VAR TableRowCause = [TotalCause]
VAR MatrixRowCause = CALCULATE ( [TotalCause], VALUES ( 'SampleSum'[Cause] ))
RETURN
OR (TableRowCause > MatrixRowCause,
AND (TableRowCause = MatrixRowCause,
'SampleSum'[Cause] <= SELECTEDVALUE ( 'SampleSum'[Cause] )
)))),
CALCULATE ( [TotalCause], ALL ( 'SampleSum'[Cause] ) ))

What is the issue, and why is it only present when publishing the report?

I have tried recreating the report in its entirety using the same data source, and the problem persists. Modifying the visuals to show the underlying items it is counting shows that exactly the same items are returned by the filters. If I replace [TotalCause] with DISTINCTCOUNT(SampleSum[ID]) in the formula for causecum%, the results are consistent after publishing, but using COUNT(SampleSum[ID]) instead causes the issue to persist.

Edit: 

ID	Confirmed?	Cause	Type	RP	site	Supplier Related	completedt
21715	Yes	ManOmm	Pro	2023-05	Ba	No	45051
21970	Yes	ManOmm	Non	2023-05	Ba	No	45065
22108	Yes	MetPro	Pro	2023-05	Ba	No	45065
22471	Yes	SUP	Non	2023-07	Ba	Yes	45124
22809	Yes	ManMem	Non	2023-06	Ba	No	45079
22864	Yes	MetPro	Pro	2023-07	Ba	No	45111
22905	Yes	ManApp	Non	2023-07	Ba	No	45118
22905	Yes	ManMem	Non	2023-07	Ba	No	45118
22905	Yes	ManOmm	Non	2023-07	Ba	No	45118
23841	Yes	ManOmm	Non	2023-09	Ba	No	45176
23922	Yes	ManApp	Pro	2023-09	Ba	No	45183
23922	Yes	ManMan	Pro	2023-09	Ba	No	45183
24428	Yes	MacMal	Non	2023-10	Ba	No	45218
24428	Yes	ManApp	Non	2023-10	Ba	No	45218

This is the smallest data set I can reproduce the error with. Import this into Power BI as an excel sheet, using the table name 'Sample'.

Then, create a summary table: 

SampleSum = SUMMARIZECOLUMNS('Sample'[ID], 'Sample'[Cause],'Sample'[site], 'Sample'[RP],'Sample'[Type],'Sample'[Confirmed?],'Sample'[Supplier Related],'Sample'[completedt], "intercause",DISTINCTCOUNT(('Sample'[ID])))

Create a new table with no data (I called mine 'Measures0'). Add these two measures to it:

TotalCause = sumx('SampleSum','SampleSum'[intercause])

causecum% = DIVIDE (
CALCULATE ([TotalCause],
FILTER (ALL ( 'SampleSum'[Cause] ),
VAR TableRowCause = [TotalCause]
VAR MatrixRowCause = CALCULATE ( [TotalCause], VALUES ( 'SampleSum'[Cause] ))
RETURN
OR (TableRowCause > MatrixRowCause,
AND (TableRowCause = MatrixRowCause,
'SampleSum'[Cause] <= SELECTEDVALUE ( 'SampleSum'[Cause] )
)))),
CALCULATE ( [TotalCause], ALL ( 'SampleSum'[Cause] )))

Create a stacked line and column graph, using SampleSum[Cause] as the x-axis, Measures0[TotalCause] as the column y-axis, and Measures0[cumcause%] as the line y-axis. Create a filter for SampleSum[RP] and include only 2023-07, 2023-09, and 2023-10. Publish to the service.

In the desktop app, it creates this graph:

In the service, it creates this graph:

[Anonymous]

I also get the same issue in several reports I publish to the service. The issue occurs in the service, but in PBI Desktop cumulative measure calculates correctly. Will this issue be addressed in January version of PBI ? It is not available yet (February 15). 

[lbendlin]

Traditionally there is no January version of Power BI Desktop or Gateway, only QFEs.  

So yes, it is possible that there are differences in the rendering engine between desktop and service (we had very frequent service updates already this year).

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

[AKirk · Accepted solution]

I believe I have found the issue. It appears to be a case of auto-exist making assumptions, as detailed here:

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

Setting filters on fields outside the summary table makes the results consistent between the desktop app and the service. The original data set uses references to other tables in a couple areas, which happen to be the areas I need to filter by.

[Anonymous]

Hi @AKirk ,

Do you also get this error when you put data into table visual on Power bi service? Have you tried uploading another pbix file that also gives an error? Ever tried refreshing your data?

Please provide more details.

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AKirk]

If I create a table using the same measures over the data, I get the same error. It will correctly show the total items for each cause code, but the cumulative total is incorrect. If I create a table to show the underlying items directly (with no aggregation), it gives the correct items. Further, I can split the measure calculation into parts. It gives a consistent result if I use only this condition: AND (TableRowCause = MatrixRowCause, 'SampleSum'[Cause] <= SELECTEDVALUE ( 'SampleSum'[Cause] ) but it gives an inconsistent result if I use only this part: TableRowCause > MatrixRowCause This error has persisted across data refreshes, I have not been able to reproduce this error using other pbix files.

[lbendlin]

Use a Quick Measure for a more robust computation of your pareto.

[AKirk]

Using a quick measure to get a running total of the number in question, totaled when the Cause changes, also produces inconsistent results. It gives the correct total in the desktop app (treating the causes in alphabetical order, instead of numerical order, but that is fixable), but it gives the wrong total when published to the service.

This is the auto-generated quick measure:

intercause running total in Cause = CALCULATE( SUM('SampleSum'[intercause]), FILTER( ALLSELECTED('SampleSum'[Cause]), ISONORAFTER('SampleSum'[Cause], MIN('SampleSum'[Cause]), DESC) ) )

In the desktop app:

In the service: