Pareto of a counting model

[humbertovianabr]

Hi experts,
I have this specific model to see the total of materials by class:

Where:
d_klah_classe.CLASS_classe: Field type text
[total_materiais]: DAX measure - "DISTINCTCOUNT(d_mara_material[MATNR_n_material])"

I want to create a pareto model, than I just created a DAX measure to give me the cumulative of column [total_materiais], but the problem comes when the value of next line in that column is the same, see:

The result I expect:

Could you help me to correct the DAX measure (bellow)?

Acumulado =

VAR vQtdMaterial = [total_materiais]

VAR vAcumulado =

IF(

    NOT(ISBLANK([total_materiais])),

    CALCULATE(

        [total_materiais],

        FILTER(

            ALLSELECTED(f_kssk_atr_objt_classe[d_klah_classe.CLASS_classe]),

            [total_materiais] >= vQtdMaterial

        )

    )

)

RETURN

IF(

    ISINSCOPE(f_kssk_atr_objt_classe[d_klah_classe.CLASS_classe]),

        vAcumulado,

        vQtdMaterial

)

FYI: The field "Class" will never repeat, so, maybe it can be used to to some comparison on the new fórmula. I do not know how to do ;(

[parry2k]

@humbertovianabr You can also leverage the new WINDOW function, here is a video on my channel showing how this can be done: How new WINDOW DAX functions and PARTITIONBY helped to get the running total in Power BI - Part 2 - ...

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[humbertovianabr]

https://drive.google.com/file/d/1EPtvLzrfZ1hXOUDtvIpKb96fZkJPdsaI/view?usp=sharing

[Ashish_Mathur]

Hi,

Please find attached the PBI file.

Hope this helps.

[lbendlin · Accepted solution]

Cumulative Material = 
var c = max(data[Class])
var sm = count(data[Material])
var s = SUMMARIZE(ALLSELECTED(data),[Class],"sm",count(data[Material]))
var s1 = filter(s,[sm]>sm || ([sm]=sm && [Class]<=c))
return if(hasonevalue(data[Class]),sumx(s1,[sm]),sumx(s,[sm]))

see attached

[humbertovianabr]

Awesome..thanks a lot @lbendlin 

[lbendlin]

The field "Class" will never repeat, so, maybe it can be used to to some comparison on the new fórmula.

You're on the right track.  Add a filter that uses that field as an alphabetical sorter.