Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Everyone,
I've successfully formulated a running percentage measure that uses the following two measures:
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
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.
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
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.
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.)
The measure I added based on the formula provided in your last message is:
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
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
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
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |