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.
I have a unique situation to filter the table based on condition and slicer and then sum of value if multiple selection in the filter.
I have a table1 with the shop name, projected sales value, and date. The date can be a past date or future date. The sample below in the image.
Table1 | ||
ShopName | ProjectedSales | Date |
Shop1 | 100000 | 01-Jan-20 |
shop1 | 200000 | 04-Apr-20 |
Shop1 | 130000 | 15-Oct-20 |
Shop2 | 240000 | 03-Jun-20 |
Shop2 | 125000 | 20-Nov-20 |
In the slicer, there are shop names coming from another dataset.
Report has KPI card that will display the value of selected shop for closest day to today in past. if multiple selection made in slicer, it should sum of value for selected shop.
For example,
if shop1 selected - > card should have value of 200,000 as date is 4 Apr closest to today and it is in past.
if shop2 selected -> card should have value of 240000 as date is 3 june closest to today, other is in future.
if both shop selected - > card should have value of 440000, sum of value for shop1 and shop2.
I have tried so many things but couldn't get the desired output.
I have created an extra column for days different and created measure as below to get the value but it is only working for a single selection, if multiple shops selected, it is not working. Please help me with this.
Rajan
Solved! Go to Solution.
You definitely need to use SUM not VALUES. The VALUES function will only return a scalar result when you have a single row in the filter context.
Given your sample data something like the following should work:
ClosestdayValueByBU = CALCULATE(
SUM('Monthly Sales Goals by BU'[ProjectedSales]),
FILTER('Monthly Sales Goals by BU' ,
'Monthly Sales Goals by BU'[Date] = CALCULATE(max('Monthly Sales Goals by BU'[Date]),
ALLEXCEPT('Monthly Sales Goals by BU','Monthly Sales Goals by BU'[ShopName]),
'Monthly Sales Goals by BU'[Date] <= NOW()
)
)
)
You definitely need to use SUM not VALUES. The VALUES function will only return a scalar result when you have a single row in the filter context.
Given your sample data something like the following should work:
ClosestdayValueByBU = CALCULATE(
SUM('Monthly Sales Goals by BU'[ProjectedSales]),
FILTER('Monthly Sales Goals by BU' ,
'Monthly Sales Goals by BU'[Date] = CALCULATE(max('Monthly Sales Goals by BU'[Date]),
ALLEXCEPT('Monthly Sales Goals by BU','Monthly Sales Goals by BU'[ShopName]),
'Monthly Sales Goals by BU'[Date] <= NOW()
)
)
)
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 |