Forum Discussion

Mack1int's avatar
Mack1int
Frequent Visitor
8 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

2 Replies

Replies have been turned off for this discussion
  • Anonymous's avatar
    Anonymous
    Not applicable

    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