Forum Discussion

Ritesh_Air's avatar
Ritesh_Air
Icon for Post Patron rankPost Patron
6 years ago
Solved

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 fo...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

     

     

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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

 

 

  • Ritesh_Air's avatar
    Ritesh_Air
    Icon for Post Patron rankPost Patron
    6 years ago

    mahoneypat 

     

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

     

     

    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

    • mahoneypat's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      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

       

       

       

      • Ritesh_Air's avatar
        Ritesh_Air
        Icon for Post Patron rankPost Patron
        6 years ago

        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