Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
refer below table
SEGMENT | CATEGORY | SUB_CATEGORY | VALUE |
EAST | A | CH | 10 |
EAST | B | AB | 20 |
EAST | C | LO | 30 |
EAST | D | MN | 10 |
EAST | E | PQ | 40 |
EAST | F | GH | 50 |
EAST | G | FL | 100 |
EAST | H | KP | 50 |
EAST | I | IO | 80 |
EAST | J | MN | 60 |
EAST | K | ZX | 70 |
EAST | L | HM | 90 |
EAST | M | RO | 45 |
WEST | N | PE | 62 |
WEST | O | SC | 34 |
WEST | P | EM | 20 |
WEST | Q | CH | 40 |
WEST | R | UV | 67 |
WEST | S | IR | 73 |
WEST | TU | RE | 50 |
WEST | X | DE | 74 |
CENTRAL | W | BC | 88 |
CENTRAL | V | HG | 92 |
CENTRAL | Y | IQ | 106 |
CENTRAL | Z | AB | 110 |
CENTRAL | K | PK | 23 |
CENTRAL | L | ST | 54 |
SOUTH | M | YS | 18 |
SOUTH | O | JS | 60 |
SOUTH | Q | SE | 30 |
SOUTH | E | PO | 40 |
SOUTH | A | CE | 20 |
SOUTH | B | HI | 10 |
SOUTH | F | LI | 200 |
SOUTH | G | BM | 50 |
SOUTH | H | GM | 150 |
SOUTH | R | HM | 20 |
SOUTH | S | TY | 30 |
SOUTH | V | UT | 50 |
Need below output,Top 3 category based on value in each segment as follows,
Solved! Go to Solution.
Hi,
Please check the below measure and the attached pbix file.
It is for creating a new measure.
Value top three: =
VAR currentsegment =
MAX ( Segment[SEGMENT] )
VAR newtable =
FILTER ( ALL ( Data ), Data[SEGMENT] = currentsegment )
RETURN
CALCULATE (
SUM ( Data[VALUE] ),
KEEPFILTERS ( TOPN ( 3, newtable, CALCULATE ( SUM ( Data[VALUE] ) ), DESC ) )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below measure and the attached pbix file.
It is for creating a new measure.
Value top three: =
VAR currentsegment =
MAX ( Segment[SEGMENT] )
VAR newtable =
FILTER ( ALL ( Data ), Data[SEGMENT] = currentsegment )
RETURN
CALCULATE (
SUM ( Data[VALUE] ),
KEEPFILTERS ( TOPN ( 3, newtable, CALCULATE ( SUM ( Data[VALUE] ) ), DESC ) )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |