Better way to create Pareto Chart?

[AKirk]

I am trying to create a pareto chart to show the number and rate of items by category and by month, but I've been running into issues.

Here are my sample data:

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

I import this into Power BI as an excel file, with the table name 'Sample'. Then, to capture items that may have more than one associated cause, I create a summary table, 'SampleSum':

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

I create a new table ('Measures0') with no data to hold these two measures:

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

Then I create a line and stacked column chart with SampleSum[Cause] as the x-axis, Measures0[TotalCause] as the column y-axis, and Measures0[cumcause%] as the line y-axis. In the desktop app, this works as expected. However, when I publish it to my workspace, the cumcause% measure works differently.

Shown in the desktop, filtered to RP = 07/01/2023, 09/01/2023, and 10/01/2023:

And exactly the same graph in my workspace, with the same filters:

What is causing this inconsistency, and is there a way to create the same functionality that will work equally well in both the desktop app and in the service?

[AKirk · Accepted solution]

This appears to be a problem caused by auto-exist, as detailed here:

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

Fortunately, as the underlying data set needs to be filtered based on fields that I can link to other tables, I can bypass this behavior by setting the filters outside the summarized table. This makes the measures behave consistently between the app and the service.

[Anonymous]

Hi @AKirk ,

You can refer to below screenshoots. In my tests, set the minimum value of the secondary y-axis to 0.3, (or customize the range you need). Then after publishing it to powerbi service everything works fine.

after publish to service, everything well done.

Best Regards,
Adamk Kong

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

[AKirk]

That didn't change anything for me.

Desktop:

After publishing to the service: