topic Re: Counting Pareto Items less than 80% in DAX Commands and Tips

Counting Pareto Items less than 80%

Talal141218 — Wed, 18 Oct 2023 13:02:04 GMT

Hallo everyone,

Please save my day. I am about to be crazy.

I want to count the items less than 80% in pareto mesure and put in a card visual. The Formel as below :

IF(VALUE(Fact_SalesInvoiceLine[Parteto %]) <= 0.8, DISTINCTCOUNT(Dim_Item[ItemNo]))

when i use this formel count all items, which we have.

Thanks in advance

Re: Counting Pareto Items less than 80%

NorbertG — Wed, 18 Oct 2023 13:05:55 GMT

CALCULATE(DISTINCTCOUNT(Dim_Item[ItemNo]),Fact_SalesInvoiceLine[Pareto %] <= 0.8)

Re: Counting Pareto Items less than 80%

Talal141218 — Wed, 18 Oct 2023 13:10:42 GMT

Thanks for your reply, but i tried this formula and it dosen't work.

Re: Counting Pareto Items less than 80%

NorbertG — Wed, 18 Oct 2023 13:21:18 GMT

How about:
CALCULATE(DISTINCTCOUNT(Dim_Item[ItemNo]),FILTER(Fact_SalesInvoiceLine[Pareto %] <= 0.8))

Re: Counting Pareto Items less than 80%

Talal141218 — Wed, 18 Oct 2023 13:31:19 GMT

Sorry, the same problem, it will be better if we use conditional function.

Re: Counting Pareto Items less than 80%

Dangar332 — Wed, 18 Oct 2023 13:57:04 GMT

hi, Talal141218

when you compare value with percantage it not work with 0.8

for that you have to provide value which consider as a percantage value

try below
CALCULATE(DISTINCTCOUNT(Dim_Item[ItemNo]),Fact_SalesInvoiceLine[Pareto %] <= 80/100)

here you can check . i have different data but it work perfectly

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

Re: Counting Pareto Items less than 80%

Talal141218 — Wed, 18 Oct 2023 14:15:09 GMT

Thanks a lot for ur reply, but the Formula dosn't work either. I write down the Pareto measure maybe helped in logic. As below :

Parteto % =

Var Total = CALCULATE(Fact_SalesInvoiceLine[CM_SalesInvoiceLine_Amount_ExclAll_LCY], ALLSELECTED(Dim_Item[ItemNo])

)

var currt = Fact_SalesInvoiceLine[CM_SalesInvoiceLine_Amount_ExclAll_LCY]

var sumtable = SUMMARIZE(ALLSELECTED(Dim_Item[ItemNo]), Dim_Item[ItemNo], "sales", Fact_SalesInvoiceLine[CM_SalesInvoiceLine_Amount_ExclAll_LCY])

var cumulsum = SUMX(FILTER(sumtable, Fact_SalesInvoiceLine[CM_SalesInvoiceLine_Amount_ExclAll_LCY] >= currt ), Fact_SalesInvoiceLine[CM_SalesInvoiceLine_Amount_ExclAll_LCY])

return DIVIDE(cumulsum,Total)

Re: Counting Pareto Items less than 80%

Dangar332 — Wed, 18 Oct 2023 15:07:15 GMT

hi, Talal141218
provide sample data and your expected output

Re: Counting Pareto Items less than 80%

Talal141218 — Thu, 19 Oct 2023 06:41:12 GMT

Hi,

I will send you a pic and the measure, which i used and already shared before. I want to count the Artikel the column in the left side less than 80% Pareto.

Thanks for your support.

Re: Counting Pareto Items less than 80%

Dangar332 — Thu, 19 Oct 2023 09:44:45 GMT

hi, Talal141218

here you use

IF(VALUE(Fact_SalesInvoiceLine[Parteto %]) <= 0.8, DISTINCTCOUNT(Dim_Item[ItemNo]))

logic in which you use two different table (Dim_Item and Fact_SalesInvoiceLine) so first check relationship bw them

you say it give when i use this formel count all items. if relationship is not correct then it might possible to give all item.

as you provide data screen short below formula work well. but it might other problem present like relationship or something else

Re: Counting Pareto Items less than 80%

Talal141218 — Fri, 20 Oct 2023 09:06:01 GMT

Thanks for your reply. I traid this Formula before and didn't work correctly. I thnik as you in your reply mentioned the count all items despit there is a relationship between tabels. Do you have an idea, how can i check the relationship or correct it?