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

Get inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.

Reply
PaulBethancourt
Frequent Visitor

Use the Last Value in a Matrix as the "Total"

We calculate and report OSHA TRIR monthly in a Matrix that has the "goal", "results" and "delta".
The challange is to have the last or latest value as the "Total"

PaulBethancourt_0-1675962554263.png

I use the current formula which is part of a Switch with about 60 metrics:

Results = (if(HASONEVALUE(T_Metric_Input_History[Metric])
,switch(values('T_Metric_Input_History'[Metric])
,"OSHA TRIR",format(average(T_Metric_Input_History[Results]),"0.00")

Any help would be greatly appreciated!

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@PaulBethancourt , use a measure like on top of you measure

 

if(not isinscope(Date[year Month]),calculate(lastnonblankvalue(Date[year Month], [Measure]),allselected(Date[year Month])),[Measure])

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
Syndicate_Admin
Administrator
Administrator

JorgeLuis27_0-1717004640681.png

I have the following table with a DAX that calculates the variation versus the previous month and I want the total to be equal to the last month with data (marked in yellow) could you help me how to modify the DAX please

KPI Sales LM Test =
WERE Difference = [Total Sales]-[Total LM Sales]
WERE Address =
SWITCH(
TRUE()
, Difference> 0, "▲"
,Difference < 0, "▼"
,"●"
)
WERE Percentage =
IF( [Total LM Sales] = 0,1,
Difference/[Total LM Sales])

RETURN
"LM: " & Address & " " & FORMAT(Percentage,"0.00%") & " | (" & FORMAT(Difference,"##,##") & ")"

amitchandak
Super User
Super User

@PaulBethancourt , use a measure like on top of you measure

 

if(not isinscope(Date[year Month]),calculate(lastnonblankvalue(Date[year Month], [Measure]),allselected(Date[year Month])),[Measure])

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

amitchandak, you are a rock star!

Thank you very much!

I made a minor change in order to use the Result column since it wasn't a measure.

I used an average of it.

,"OSHA TRIR",format(if(not isinscope('Date table'[YRMO]),calculate(lastnonblankvalue('Date table'[YRMO],average(T_Metric_Input_History[Results])),allselected('Date table'[YRMO])),average('T_Metric_Input_History'[Results])),"0.00")

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code FABINSIDER for a $400 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

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

March2025 Carousel

Fabric Community Update - March 2025

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