Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
humbertovianabr
New Member

Pareto of a counting model

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

humbertovianabr_0-1692037696147.png

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:

humbertovianabr_1-1692038150219.png


The result I expect:

humbertovianabr_2-1692038458679.png


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 ;(

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

lbendlin_0-1692217736329.png

 

see attached

 

View solution in original post

7 REPLIES 7
parry2k
Super User
Super User

@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! ❤️



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @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! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Ashish_Mathur
Super User
Super User

Hi,

Share the download link of your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

Please find attached the PBI file.

Hope this helps.

Ashish_Mathur_0-1692229620386.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

lbendlin_0-1692217736329.png

 

see attached

 

Awesome..thanks a lot @lbendlin 

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.