Forum Discussion

MStP's avatar
MStP
Helper I
4 years ago

Blank First Row When Calculating Running Percentage

Hello Everyone,   I've successfully formulated a running percentage measure that uses the following two measures:   PERCENT SPEND MEASURE 1 = var allspend = CALCULATE( [SPEND MEASURE], ALLSELE...
Need Help
v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

Hi MStP ,

 

I made some changes based on Jihwan_Kim's formula and replaced "ALL" in the formula with "ALLSELECT". Please try the following formula to see if it works.

 

Sales: = SUM( Data[Sales] )
Sales percentage: = 
var allsales = CALCULATE( [Sales:], ALLSELECTED(Data))
return
DIVIDE( [Sales:], allsales )
Sales percentage cumulate: = 
VAR current_row_percentage = [Sales percentage:]
VAR percentagetable_by_vendors =
    FILTER (
        ADDCOLUMNS ( ALLSELECTED(Data[Vendors]), "@salespercentage", [Sales percentage:] ),
        [@salespercentage] >= current_row_percentage
    )
RETURN
    SUMX ( percentagetable_by_vendors, [@salespercentage] )

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

MStP's avatar
MStP
Helper I
4 years ago

Hi Winniz,

 

I had previously changed the formula to ALLSELECTED and that is producing the results screen captured in my original post. I have included a file link to sample data in my other reply - I'm not sure how to get the same kind of download link you provided. It looks like I need special permission.

 

Moreover, I'm not experiencing errors in the DAX so there is no error information, just inconsistent application of the formula depending on the plant/date selected.

 

Michael

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi MStP ,

     

    Please try this measure to see if it meets your data.

     

    Measure = 
var sum_cumulate = 
    CALCULATE( 
        SUM(Data[Sales]), 
        FILTER(
            ALLSELECTED(Vendors),
            Vendors[Vendors] <= MAX(Vendors[Vendors])
        )
    ) 
var sum_total = SUMX( ALLSELECTED(Data), Data[Sales] )
return 
    IF(
        SUM(Data[Sales]) <> BLANK(), 
        sum_cumulate / sum_total
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • MStP's avatar
      MStP
      Helper I
      4 years ago

      OK, this new measure didn't work for me but perhaps I'm applying the incorrect tables/columns. If you'll allow me, I could provide some reference:

       

      My data is being pulled from two sources:

      ZQV_ME2N = Purchase Order Table (analogous to the Data sample table)

      ZQV_ME2N[Net Order Value] (analogous to the Data[Sales] sample column)

      ZQV_LFA1_ATC = Vendor Table (analogous to the Vendors sample table)

      ZQV_LFA1_ATC[Vendor] (analogous to the Vendors[Vendors] sample column)

       

      The measures being applied are:

      1.)

      SPEND MEASURE =
      SUM( ZQV_ME2N[NetOrderValue])
       
      This is analogous to the Sales: measure in the sample file:
      Sales: =
      SUM( Data[Sales] )
       
      No issues here, correct? 
      2.)
      PERCENT SPEND MEASURE 1 =
      var allspend = CALCULATE( [SPEND MEASURE], ALLSELECTED (ZQV_ME2N))
      return
      DIVIDE( [SPEND MEASURE], allspend)
       
      This is analogous to the Sales Percentage measure in the sample file.
      Sales percentage: =
      var allsales = CALCULATE( [Sales:], ALLSELECTED (Data))
      return
      DIVIDE( [Sales:], allsales )
       
      No issues here, correct?
       
      3.)
      Running Percentage: =
      VAR current_row_percentage = [PERCENT SPEND MEASURE 1]
      VAR percentagetable_by_vendors =
      FILTER (
      ADDCOLUMNS ( ALLSELECTED ( ZQV_LFA1_ATC ), "@percentspendmeasure1", [PERCENT SPEND MEASURE 1] ),
      [@percentspendmeasure1] >= current_row_percentage
      )
      RETURN
      SUMX ( percentagetable_by_vendors, [@percentspendmeasure1] )
       
      This is analogous to the Sales percentage cumulate in the sample file:
      Sales percentage cumulate: =
      VAR current_row_percentage = [Sales percentage:]
      VAR percentagetable_by_vendors =
      FILTER (
      ADDCOLUMNS ( ALLSELECTED ( Vendors ), "@salespercentage", [Sales percentage:] ),
      [@salespercentage] >= current_row_percentage
      )
      RETURN
      SUMX ( percentagetable_by_vendors, [@salespercentage] )
       
      Perhaps this is where there is some disconnect that is producing the blank first row for some filter criteria, but most everything works with these measures I use - the DAX expressions are correct and I receive the expected data except for the blank first row when applying SOME of the filters.

       

      The measure I added based on the formula provided in your last message is:

      Running Percentage 1: =
      var sum_cumulate =
      CALCULATE(
      SUM(ZQV_ME2N[NetOrderValue]),
      FILTER(
      ALLSELECTED(ZQV_LFA1_ATC),
      ZQV_LFA1_ATC[Vendor] <= MAX(ZQV_LFA1_ATC[Vendor])
      )
      )
      var sum_total = SUMX( ALLSELECTED(ZQV_ME2N), ZQV_ME2N[NetOrderValue] )
      return
      IF(
      SUM(ZQV_ME2N[NetOrderValue]) <> BLANK(),
      sum_cumulate / sum_total
      )

       

      This latest measure provides does not work as shown below. The results of everything added above produces this for Plant 1 - everything (not including the last measure) is correct except for the blank first row for the first Running % column:

       

      Plant 2 - everything besides the most recent measure works fine and there is NO difference in the formula or plant filter used. The underlying data for both plants is correct.

       

       

      Please forgive me if I am not providing the required details - as I mentioned I am new at this and extremely grateful for the assistance.

       

      Michael

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        4 years ago

        Hi MStP ,

         

        The formula I provided is cumulative by Vendors column, so please sort the Vendors columns in the Vendors table in ascending order and see if the measure is calculated correctly.

         

         

        Also, I think the Jihwan_Kim' formula you are using is correct and returns the correct result in my sample data. But based on your screenshot, I noticed that when you select Plant 1, it calculates the accumulation without the current row, i.e. 13%=4%+9%, 17%=4%+9%+4%.

        There is no summation of the current row in Running%, which should cause the first row to have a null value. So please make sure you have ">=" instead of ">" in your formula.

         

          

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz