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.
Good Morning All,
I am struggling with this formula it works for when the number of rows is 2 and the cumulated segmentation however when i put in the the extra count of 3 rows it doesn't classify them "A","B" and "C", instead it comes up with an error. Any help would be greatly appreciated.
Many Thanks
ABC Product =
VAR NoOfRows =
COUNTROWS (
CALCULATETABLE ( ABC, ALLEXCEPT ( ABC, ABC[Location], ABC[Brand] ) )
)
RETURN
SWITCH (
TRUE (),
NoOfRows = 2, IF (
RANKX (
CALCULATETABLE ( ABC, ALLEXCEPT ( ABC, ABC[Location], ABC[Brand] ) ),
ABC[Sales Revenue]
)
= 1,
"A",
"B"
),
NoOfRows = 3, IF (
RANKX (
CALCULATETABLE ( ABC, ALLEXCEPT ( ABC, ABC[Location], ABC[Brand] ) ),
ABC[Sales Revenue]
)
= 1,
"A",
"B",
“C”
),
ABC[Cumulated Percentage] <= 0.8, "A",
ABC[Cumulated Percentage] <= 0.95, "B",
"C"
)
Solved! Go to Solution.
@Mack1int,
Please change your DAX formula to the following:
ABC Product = VAR NoOfRows = COUNTROWS ( CALCULATETABLE ( ABC, ALLEXCEPT ( ABC, ABC[Location], ABC[Brand] ) ) ) RETURN SWITCH ( TRUE (), NoOfRows = 2, IF ( RANKX ( CALCULATETABLE ( ABC, ALLEXCEPT ( ABC, ABC[Location], ABC[Brand] ) ), ABC[Sales Revenue] ) = 1, "A", "B" ), NoOfRows = 3, IF ( RANKX ( CALCULATETABLE ( ABC, ALLEXCEPT ( ABC, ABC[Location], ABC[Brand] ) ), ABC[Sales Revenue] ) = 1, "A", IF ( RANKX ( CALCULATETABLE ( ABC, ALLEXCEPT ( ABC, ABC[Location], ABC[Brand] ) ), ABC[Sales Revenue] ) = 2,"B", "C" )), ABC[Cum Percent] <= 0.8, "A", ABC[Cum Percent] <= 0.95, "B", "C" )
Regards,
Lydia
@Mack1int,
Please change your DAX formula to the following:
ABC Product = VAR NoOfRows = COUNTROWS ( CALCULATETABLE ( ABC, ALLEXCEPT ( ABC, ABC[Location], ABC[Brand] ) ) ) RETURN SWITCH ( TRUE (), NoOfRows = 2, IF ( RANKX ( CALCULATETABLE ( ABC, ALLEXCEPT ( ABC, ABC[Location], ABC[Brand] ) ), ABC[Sales Revenue] ) = 1, "A", "B" ), NoOfRows = 3, IF ( RANKX ( CALCULATETABLE ( ABC, ALLEXCEPT ( ABC, ABC[Location], ABC[Brand] ) ), ABC[Sales Revenue] ) = 1, "A", IF ( RANKX ( CALCULATETABLE ( ABC, ALLEXCEPT ( ABC, ABC[Location], ABC[Brand] ) ), ABC[Sales Revenue] ) = 2,"B", "C" )), ABC[Cum Percent] <= 0.8, "A", ABC[Cum Percent] <= 0.95, "B", "C" )
Regards,
Lydia
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |