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
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 ! 🙂
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.
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/
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
107 | |
89 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |