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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Maria7
Frequent Visitor

The measure not showing the total when i don't include the date column in the visual

Hi,

I need help with a problem I'm having with displaying the total of a measure in a matrix visual when I try to filter it. The measure should show the difference of a value column between 2 years that are in the table.

Also, in the page I have a filter that contains only the Year from the data column, and when I try to filter the table with the measure in it, nothing is displayed. But when I include the data column in the visual and display the whole date, it's showing the corect result per each month but the total still isn't displayed.

 

Here is the formula :

Evolution =
VAR SelectedYear = YEAR(MAX(Table[Date]))
VAR PreviousYear1= SelectedYear - 1
VAR SelectedCategory = SELECTEDVALUE(Table[Category])
VAR SelectedProduct = SELECTEDVALUE(Table[Product])
VAR SelectedSubset = SELECTEDVALUE(Table[Subset])
VAR SelectedSubcategory = SELECTEDVALUE(Table[Subcategory])
VAR Selectedtype = SELECTEDVALUE(Table[Type])

VAR PreviousValue =
CALCULATE(
SUMX(Table, Table[Quantity]),
YEAR(Table[Date]) = PreviousYear1,
MONTH(Table[Date])= MONTH(SELECTEDVALUE(Table[Date])),
DAY(Table[Date])= DAY(SELECTEDVALUE(Table[Date])),
Table[Category] = SelectedCategory,
Table[Product] = SelectedProduct,
Table[Subset] = SelectedSubset,
Table[Subcategory]=SelectedSubcategory,
Table[Type] = Selectedtype
)
VAR CurrentValue =

CALCULATE(
SUMX(Table, Table[Quantity])),
YEAR(Table[Date]) = SelectedYear,
MONTH(Table[Date])= MONTH(SELECTEDVALUE(Table[Date])),
DAY(Table[Date])= DAY(SELECTEDVALUE(Table[Date])),
Table[Category] = SelectedCategory,
Table[Product] = SelectedProduct,
Table[Subset] = SelectedSubset,
Table[Subcategory]=SelectedSubcategory,
Table[Type] = Selectedtype
)

RETURN

IF(
PreviousValue > 0,
DIVIDE(CurrentValue - PreviousValue , PreviousValue * 100),
0
)

What is wrong with this formula? If someone can help, thank you ! 🙂

2 REPLIES 2
Maria7
Frequent Visitor

Hi @mlsx4 ,

Thank you for your answer!

But is there another way to rewrite this formula and not depend on selected values? I tried to minimize the formula and keep only the year variable, and the result of the formula is different. It gives the result, for example, for March 2024, even though I have no data for March 2023.

mlsx4
Super User
Super User

Hi @Maria7 ,

 

If nothing is selected, then the measure will not work since it depends on a selected value. There's no problem with the formula. You should check what to do if any of the selected values are blank.
Look at how it works: https://dax.guide/selectedvalue/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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