Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Last value of a calculated column based on slicer options.

Hi I need help with forming a measure.

My scenerio:

LocationDateItemquantity soldTotal item sold for the day%of the total sold for the day
East1/1/2020Burger5010050
West1/1/2020Burger4010040
East1/1/2020Fries5010050
West1/1/2020Fries6010060
East2/1/2020Burger8010080
West 2/1/2020Burger7010070
East 2/1/2020Fries2010020
West 2/1/2020Fries3010030
East3/1/2020Burger6510065
West3/1/2020Burger7510075
East3/1/2020Soda3510035
West3/1/2020Soda2510025

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/202030

 

I'm having trouble coming up with the measure. 

I tried 

LOOKUPVALUE('data'[% of total sold for the day],'data'[Date], [Latest Date])
but it gave me an error, I think it is because the column % of total sold for the day is a calculated column.
Tried lastnonblankvalue too but my %of the total sold for the day is a column not a measure.
Do you guys have any measure/DAX/ways/ideas so that I am able to see the most recent value based on teh slicer options?
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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)))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.