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 people,
I am trying for two days, without succes. Search the forums here a lot, it helped but still not reaching my final goal.
Target is to display a YTD Average processed for a specific filtered date (in a card visual).
I have a table like this (lets call it 'data'😞
Plant | Date | Campaign Day | Processing |
A | 01.01.2017 | 1 | 1000 |
B | 01.01.2017 | 1 | 500 |
C | 02.01.2017 | 1 | 100 |
A | 02.01.2017 | 2 | 900 |
B | 02.01.2017 | 2 | 400 |
C | 03.01.2017 | 2 | 200 |
I made two measure for and calculated fairly easy the cumulative processed and cumulative days.
1. Cumulative processed
2. Cumulative days
Also the YTD avg. processed I calculated via
SUMX(FILTER(VALUES(data[Plant]);[Cumulative processed]/[Cumulative Days]);[Cumulative processed]/[Cumulative Days])
in order to get the sum of total averages.
Issue: when filtering on a date (for example 03.01.2017), because A and B plants have no more processing, the YTD is not anylonger calculating average processed for all, but only for Plant C and only for the day in filter.
I then tried with:
CALCULATE([Cumulative processing]/[Cumulative Days];FILTER(DimDate;DimDate[Date]>=MIN(DimDate[Date]));FILTER(DimDate;DimDate[Date]<=MAX(DimDate[Date])))
but this doesn't return the sum of total averages, meaning total processed YTD for all A+B+C.
Hope I was clear
How can I make this work?
Thanks!
Alex
Solved! Go to Solution.
a rushed reply; in your DAX I don't see an ALL function (or one of its variations ALLSELECTED) .... you need that in your filter clause to break out of the row context and collect data from all rows... there's some good Cumulative videos at Enterprise DNA N.Zealand and lot's of dialog on this.
Hi Alex,
Did you mean that the result would be the total of the latest average of each plant even you choose the date "03.01.2017"?
It would be 633.33 + 300 + 100 = 1033.33. Right? What is formula of [Cumulative processed] and [Cumulative Days]?
Best Regards!
Dale
Dale,
formulas are as follow:
Cumulative Processing = CALCULATE(Sum(data[Processing]);FILTER(ALL(DimDate[Date]);DimDate[Date] <= Max(DimDate[Date])))
Cumulative Days = CALCULATE(COUNT(data[Processing]);FILTER(ALL(DimDate[Date]);DimDate[Date] <= Max(DimDate[Date])))
the table for processing and days retrieves the cumulative correctly when the filter for Date is active (ex. 3/1/2017).
The issue appears when trying to calculate the YTD avg. processed per day with this formmula:
SUMX(FILTER(VALUES(data[Plant]);[Cumulative Processing]/[Cumulative Days]);[Cumulative Processing]/[Cumulative Days])
This returns the SUM of all plants averages per day (which I want) but it only returns for the Plant where it finds data (Plant C).
EDIT: solved it with solution from CahabaData, thanks!!! I needed an ALLSELECTED in the average calculation:
SUMX(FILTER(ALLSELECTED(data[Plant]);[Cumulative Processing]/[Cumulative Days]);[Cumulative Processing]/[Cumulative Days])
a rushed reply; in your DAX I don't see an ALL function (or one of its variations ALLSELECTED) .... you need that in your filter clause to break out of the row context and collect data from all rows... there's some good Cumulative videos at Enterprise DNA N.Zealand and lot's of dialog on this.
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |