Forum Discussion

MStP's avatar
MStP
Icon for Helper I rankHelper 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], ALLSELECTED (ZQV_ME2N))
return
DIVIDE( [SPEND MEASURE], allspend)
 
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] )
 
The solution above was provided by Jihwan_Kim 
The data is correct when applying a plant filter, but only for one plant. The other plant and both plants together returns a blank first row in the running total.
 
Correct Calculation for Plant 1:

 

Incorrect Calculation for Plant 2:

Furthermore, if I filter by dates, some dates work, others don't.

I can't figure out why the slicers being applied only work for certain filtered criteria. 

Thank you for any assistance provided.

 

Best,

Michael

 

 

Need Help

9 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity 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
      Icon for Helper I rankHelper 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
        Icon for Community Support rankCommunity 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.