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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mack1int
Frequent Visitor

Formula Classification Help

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"
)

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@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"
)

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@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"
)

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you so much @v-yuezhe-msft

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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