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.
Hi I need help with forming a measure.
My scenerio:
Location | Date | Item | quantity sold | Total item sold for the day | %of the total sold for the day |
East | 1/1/2020 | Burger | 50 | 100 | 50 |
West | 1/1/2020 | Burger | 40 | 100 | 40 |
East | 1/1/2020 | Fries | 50 | 100 | 50 |
West | 1/1/2020 | Fries | 60 | 100 | 60 |
East | 2/1/2020 | Burger | 80 | 100 | 80 |
West | 2/1/2020 | Burger | 70 | 100 | 70 |
East | 2/1/2020 | Fries | 20 | 100 | 20 |
West | 2/1/2020 | Fries | 30 | 100 | 30 |
East | 3/1/2020 | Burger | 65 | 100 | 65 |
West | 3/1/2020 | Burger | 75 | 100 | 75 |
East | 3/1/2020 | Soda | 35 | 100 | 35 |
West | 3/1/2020 | Soda | 25 | 100 | 25 |
The total item sold for the day column and %of the total sold of the day are calculated columns.
their DAX are
Total item sold for the day = Calculate(SUM(('data'[Quantity sold]),ALLEXCEPT('data','data'[location],'CD3,4,5'[Date]))
%of the total sold of the day = DIVIDE('data'[Quantity sold],'data'[Total item sold for the day])
I have 2 slicers, one controlling the Item, and the other controlling the location.
slicer option seletec: Item = Fries, Location = West
I have a measure created for latest date
Latest Date = CALCULATE(LASTDATE('data'[Date]),ALLEXCEPT('date','data'[Item],'data'[location])
Now I need another measure to show the cell for %of the total sold for the day for that latest date.
So i can have the following table visual:
Latest Date | %of the total sold for the day |
2/1/2020 | 30 |
I'm having trouble coming up with the measure.
I tried
Solved! Go to Solution.
Thank you for help so far, but I already found an answer by myself already, which is to use the KPI visual, with '% of the total of the day' as the indicator and 'Date' as the trend axis. The inidcator shows the latest value of the % of the total, with location slcier and menu item slicer selected an option each.
@Anonymous , Kindly let us know if this posts is answered:
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
@Anonymous , Try like
divide(CALCULATE(lastnonblankvalue('data'[Date],sum(table[Total item sold for the day])),ALLEXCEPT('date','data'[Item],'data'[location])),calculate(sum(table[Total item sold for the day]) ,all(table)))
Hi thank you for reply but when I implemented it, it gave 0% for all slicer options.
I really think the answer is
LOOKUPVALUE('data'[%of the total sold for the day],'data'[date],[Latest Date])
But it shows ' cant display the visual' for all visuals
Thank you for help so far, but I already found an answer by myself already, which is to use the KPI visual, with '% of the total of the day' as the indicator and 'Date' as the trend axis. The inidcator shows the latest value of the % of the total, with location slcier and menu item slicer selected an option each.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |