Production Pareto does not work if select more than 1

[vincentakatoh]

Hi, 

Created a Pareto Chart using below DAX. 

New Table 

Pareto = SUMMARIZE(
data,
Data[Area],
Data[Line],
Data[station],
"DefectFn",sum(Data[TR])
)

New column

rank = RANKX(ALLSELECTED('Pareto'),'Pareto'[DefectFn],,desc)

New meaure

AccumTotal = CALCULATE(SUM('Pareto'[DefectFn]), filter(ALLSELECTED('Pareto'),'Pareto'[rank]<=MAX('Pareto'[rank])))

The Pareto works well when I select a specific line.

Nontheless. the Pareto fails is I select all lines.

Help needed: Need help to change the DAX such that Pareto will not fail if I select all lines. 

[v-yuezhe-msft]

Hi @vincentakatoh,

Do you want to create cumulative running total based on highest DefectFn? If so, create the following measures in your table.

Measure = SUM(Pareto[DefectFn])
AccumTotal1 = IF(NOT(ISBLANK([Measure])),CALCULATE([Measure],FILTER(ALL(Pareto[Station]),SUM(Pareto[DefectFn])<=[Measure])))


If the above DAX doesn't help, please share sample data of your table and post expected result here.

Thanks,
Lydia Zhang

[vincentakatoh]

Hi @Lydia, 

Thanks. Very close. But how to show the AccumTotal1 as a percentage of grand total? Need to show the line in a percentage. 

Uploaded sample data

[v-yuezhe-msft]

Hi @vincentakatoh,

You can create percentage using the following DAX.

Measure 2 = IF(NOT(ISBLANK([Measure])),CALCULATE([Measure],ALL(Pareto[Station])))
%GT ActualToal1 = [AccumTotal1]/[Measure 2]

Regards,
Lydia Zhang

[vincentakatoh]

Hi Lydia, 

Thanks again. Can you attached your sample file for my reference? I'm getting an error msg on Measure 2. 

Thanks for helping a DAX dummy like me.

[v-yuezhe-msft · Accepted solution]

@vincentakatoh,

You can download my PBIX file from the link below.

https://1drv.ms/u/s!AhsotbnGu1NogmH4VMeo7BOjP_Rf

Regards,
Lydia

[vincentakatoh]

Hi Lydia, 

Thanks again. Can you attached your sample file for my reference? I'm getting an error msg on Measure 2. 

Thanks for help a DAX dummy like me 😄