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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ritesh_Air
Post Patron
Post Patron

Sumx, Cumulative and blank rows

Hi,

 

I am trying to get to 80% of total sales, which I explained in different thread (I posted that link in the bottom). I am close but not there yet. Want to ask for specific issues to keep it focused on this thread.

 

Here I created the visual with TOPN, it could get me to 80% of sale but users need to click on different topNs to get there which is cumbersome.

 

Dyanmic 80%.PNG

 

Here are my questions:

 

1. How do I add Total which is 7.1M at TOTAL row? It doesn't calculate dynamically. I had to create it separately and create a card (daxCumulative).

2. How do I have cumulative column which is like running total?

3. If I add any other column, then I see bunch of empty rows, like this, how do I avoid that?

 

Empty rows.PNG

 

 

Here are my formulas:

 

daxCumulative = 
CALCULATE (
    SUM ( 'CPP POS'[Sales Amount] ),
    FILTER (
        ALLSELECTED( GROWER[CRM_PARENT_GROWER_MASTER_KEY] ),
        [RANK INR] <= [SelectedtopNValue]
    )
)

 

% OF TOTAL SALE = DIVIDE([daxCumulative],SUM('CPP POS'[Sales Amount]))

 

CUMULATIVE SALES = 
IF(HASONEVALUE(GROWER[CRM_PARENT_GROWER_MASTER_KEY]),
IF (
    [RANK INR] <= [SelectedtopNValue],
    SUM ( 'CPP POS'[Sales Amount] ),
   BLANK()
)
)

 

RANK INR = 
 if(
    HASONEVALUE ( GROWER[CRM_PARENT_GROWER_MASTER_KEY] ),
    RANKX (
        ALLSELECTED ( GROWER[CRM_PARENT_GROWER_MASTER_KEY] ),
        CALCULATE ( SUM ( 'CPP POS'[Sales Amount] ) ),
        ,
        DESC,
        DENSE
    )
)

 

Here is what I started it originally:

 

https://community.powerbi.com/t5/Desktop/80-to-Total-Sale/m-p/1307389#M567932

 

Thanks,

Ritesh

 

 

1 ACCEPTED SOLUTION

Never had a measure named after me before.  Cool.  My bad, I meant to use ALL or ALLSELECTED instead of VALUES.  With VALUES, every row is in its own Top X.  Replace VALUES with ALL and it should work.

 

Regards,

Pat

 

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
mahoneypat
Employee
Employee

I would suggest a measure like this one to get your Top selected and have the total show up as the sum of those.

 

 

NewMeasure =
VAR topX = [SelectedTopNValue]
RETURN
    CALCULATE (
        SUM ( 'CPP POS'[Sales Amount] ),
        KEEPFILTERS (
            TOPN (
                topX,
                VALUES ( Grower[Parent Grower] ),
                CALCULATE ( SUM ( 'CPP POS'[Sales Amount] ) ), DESC
            )
        )
    )

 

 

Also, you can get rid of the extra rows in your Rank measure (if needed) by using an IF.

 

Rank Show Top X = IF([Rank Measure] <= [SelectedTopNValue], [Rank Measure])

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat 

 

Thanks. It works for Total but selects everything instead of (say top 3) in below example.

 

mahoneypat.PNG

 

MaHoneyPat = 
VAR topX = [SelectedTopNValue]
RETURN
    CALCULATE (
        SUM ( 'CPP POS'[Sales Amount] ),
        KEEPFILTERS (
            TOPN (
                topX,
                VALUES ( GROWER[CRM_PARENT_GROWER_MASTER_KEY]),
                CALCULATE ( SUM ( 'CPP POS'[Sales Amount] ) ), DESC
            )
        )
    )

 

Thanks,

ritesh

Never had a measure named after me before.  Cool.  My bad, I meant to use ALL or ALLSELECTED instead of VALUES.  With VALUES, every row is in its own Top X.  Replace VALUES with ALL and it should work.

 

Regards,

Pat

 

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat 

 

hahaha. I may have to create a separate folder with all of your solutions. 🙂 Much appreciated.

 

Thanks. It worked like a charm.

 

Now if we move on to #3 on my original question, why RANK INR, doesn't respect and gives me all the empty rows instead of stopping at how many values have been selected?

 

Thanks,

ritesh

 

 

Did you see my earlier suggestion to wrap your [Rank IR] measure in an IF to compare it against the selected TopN value?  Your RankIR measure is not currently set up to limit rows, and will return a result for all rows.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Greg_Deckler
Super User
Super User

1. That sounds like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 

2. I would start with the running total Quick Measure. Is this a running total based upon a RANKX measure? This may help if it is: This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

 

3. You could create a "Selector" measure and filter on it. See The Complex Selector: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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