Blank First Row When Calculating Running Percentage

[MStP]

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

[v-kkf-msft]

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]

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]

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]

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]

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

[MStP]

Hi @v-kkf-msft 

Per my last comment of 12/16 12:17...any further thoughts on this issue based on my latest comments? Thanks!

Michael

[MStP]

Hi @v-kkf-msft 

The formula you provided does work if the visual is sorted in ascending order by vendor - this obviously causes the formula provided by Jihwan Kim to error out:

I have also confirmed that in the formula he provided I do have >= in the formula:

I should mention that 99% of the time we are going to be sorting Total Spend in descending order, which again, works some of the time with Jihwan Kim's formula. We are usually using this visual to identify top vendors by spend and determine the top 80% of vendors by spend - so it's important that the calculation work for summation by total spend. If this would make a difference in the formula you provided, please update that to reflect our needs as stated above and we can try that.

Again, I am at a loss as to why some filters work but others don't. Doesn't that seem odd? There is no cause I can detect. I have recreated the slicers/pie charts and still receive the same results. The underlying data is also free from errors. I have also tried simply creating another measure with the same formula just to eliminate that small possibility but to no effect.

I sincerely appreciate your continued assistance with this. 

Michael

[lbendlin]

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.

[MStP]

Hello,

Please see the sample file attached. Everything works as expected here - I can't replicate the issue.
https://drive.google.com/file/d/15m14GMW5a312fHtXG4JQkiUY9bqFTc7a/view?usp=sharing