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 have a table Veh_classif_Standardized, in which each row is a unique customer identified by the field VIN.
Each customer has a group field (group 1, 2 or 3) and a total amount spent field.
On my report, I want to create a VIN slicer so that when i click on a VIN, I get the customer's total amount spent on a card and the total amount spent average of his group on a second card.
To do so, I created 2 DAX measures :
CurrentGroup = SELECTEDVALUE(Veh_classif_Standardized[Groupe n°])
SpentAverageCurrentGroup = CALCULATE(AVERAGE(Veh_classif_Standardized[TotalSpent]);FILTER(Veh_classif_Standardized;Veh_classif_Standardized[Groupe n°] = [CurrentGroup]))
However this does not seem to work : when I change in the slicer from a customer in a group to another customer in another group, the card with SpentAverageCurrentGroup always gives me the average of the entire table and not the average of the current group.
I would like to know what the corrrect way to do this is.
Any help much appreciated !
Hi,
would be better if we could get a sample of data to work with, but off the top of my head, could you try moving your var declaration inside your calculate statement, maybe this would work?
CALCULATE ( VAR CurrentGroup = SELECTEDVALUE ( Veh_classif_Standardized[Groupe n°] ) RETURN AVERAGE ( Veh_classif_Standardized[TotalSpent] ); Veh_classif_Standardized[Groupe n°] = CurrentGroup )
with CALCULATE, you don't always have to explicitly use the FILTER function
try
SpentAverageCurrentGroup = CALCULATE ( AVERAGE(Veh_classif_Standardized[TotalSpent]), Veh_classif_Standardized[Groupe n°] = [CurrentGroup] )
You could also try to put code for CurrentGroup into a variable call within a single expression
SpentAverageCurrentGroup = var CG = SELECTEDVALUE(Veh_classif_Standardized[Groupe n°]) return CALCULATE ( AVERAGE(Veh_classif_Standardized[TotalSpent]), Veh_classif_Standardized[Groupe n°] = [CurrentGroup] )
Hope this helps
David
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 |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |