Pareto table

Question

Dear all!I need you support please!I've been working with this code to make a Pareto:https://www.daxpatterns.com/abc-classification/&nbsp;I've been applying this solution:&nbsp;"ABC Class =IF (&nbsp; &nbsp; HASONEVALUE(Cliente[Cliente]),&nbsp; &nbsp; VAR SalesByProduct =&nbsp; &nbsp; &nbsp; &nbsp; CALCULATETABLE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ADDCOLUMNS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE(Cliente, Cliente[Cliente]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "@ProdSales", [Total Venta Analitico]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED (Cliente[Cliente])&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VAR AllSales =&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; [Total Venta Analitico], ALLSELECTED(Cliente))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VAR CurrentSalesAmt = [Total Venta Analitico]&nbsp; &nbsp; VAR CumulatedSales =&nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SalesByProduct,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [@ProdSales] &gt;= CurrentSalesAmt&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR CumulatedSalesAmount =&nbsp; &nbsp; &nbsp; &nbsp; SUMX (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CumulatedSales,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [@ProdSales]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR CurrentCumulatedPct =&nbsp; &nbsp; &nbsp; &nbsp; DIVIDE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CumulatedSalesAmount,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AllSales&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR Result =&nbsp; &nbsp; &nbsp; &nbsp; SWITCH (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TRUE,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ISBLANK ( CurrentCumulatedPct ), BLANK (),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CurrentCumulatedPct &lt;= 0.81, "A",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CurrentCumulatedPct &lt;= 0.9, "B",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "C"&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; Result"And I've two doubts:1 - How can I insert a paremeter to make it dinamic? I'd like to select from: Family, client and so on dinamically.2 - To use the ABC as a filter y make a new table but when I try to relationate it with the Client table I obtain an error: Circular dependence. Do you know why?Pareto&nbsp;

luis_caston · Answer

Dear all,I've seen that to make it dinamyc an avoid this error: Calculation error in measure Param: Column Param is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.I'm following this example:&nbsp;Error solution&nbsp;&nbsp; and I'm changing the beginning as follows:ABC Class =IF (&nbsp; &nbsp; SELECTCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Parámetro', 'Parámetro'[Parámetro], 'Parámetro'[Parámetro Campos]), at the example indicates here "Parameter[Parameter]" but I'm not able to type it.Do you know why?

amitchandak · Answer

Luis_Caston , Please check out my approach, I have used numeric parameter at one place to make it dynamic
Power BI Window: Pareto Analysis Again, 80% of sales, Order by Measure when REL position is used: https://youtu.be/GpoITi_tRIw
&nbsp;
Power BI ABC Analysis using Window function, Dynamic Segmentation: https://youtu.be/A8mQND2xSR4
&nbsp;

luis_caston · Answer

Dear amitchandak !Thank you for your support.I've followed the example and ok I've figures the second point is: How do I make it dinamical?At this part of the code I've tested it by client and I works:Net Count ABC =&nbsp; &nbsp; SUMX( 'ABC Bucket',&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Cliente[Cliente]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [ABC V2 Luis] = MAX('ABC Bucket'[ABC])),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Total Venta Analitico])))&nbsp;But If if change "Cliente[Cliente]" by a 'Parameter[Parameter] I obtain the next error:Calculation error in measure [Net Count ABC]: Column [Parameter] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

luis_caston · Answer

Following this approach I've read about the error with the paremeter: Calculation error in measure ‘Sales'[Selection]: Column [Parameter] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

Do you know How I can replace this Hasonvalue: "ABC Class =
IF (
HASONEVALUE(Cliente[Cliente])," to avoid the error?

Forum Discussion

Pareto table

4 Replies

Related content

Help with Pareto chart

Pareto Chart

Calculate Pareto quickly

Dynamic pareto table

Pareto Diagram