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

       

       

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

        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