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.
Dear Experts,
I have a table like below:
Zone | Brand | Customer | Bill Amount |
NORTH | BRAND-002 | [1000000216] | 100 |
EAST | BRAND-002 | [1000000229] | 91 |
EAST | BRAND-002 | [1000000231] | 44 |
NORTH | BRAND-002 | [1000000280] | 40 |
EAST | BRAND-001 | [1000000220] | 39 |
NORTH | BRAND-002 | [1000000257] | 33 |
NORTH | BRAND-002 | [1000000220] | 29 |
EAST | BRAND-001 | [1000000216] | 22 |
EAST | BRAND-002 | [1000000477] | 22 |
NORTH | BRAND-001 | [1000000414] | 19 |
EAST | BRAND-001 | [1000000229] | 12 |
EAST | BRAND-002 | [1000000230] | 10 |
NORTH | BRAND-001 | [1000000400] | 10 |
EAST | BRAND-002 | [1000000230] | 8 |
NORTH | BRAND-002 | [1000000400] | 8 |
EAST | BRAND-001 | [1000000230] | 6 |
NORTH | BRAND-001 | [1000000248] | 5 |
NORTH | BRAND-001 | [1000000231] | 4 |
EAST | BRAND-001 | [1000000224] | 3 |
EAST | BRAND-001 | [1000000231] | 2 |
NORTH | BRAND-001 | [1000000406] | 2 |
NORTH | BRAND-001 | [1000000229] | 0.5 |
I need to calculate a measure - Total Billed Amount for Top 3 Customers and want to plot the measure for each Zone.
Brand can be selected from a slicer (Multiple selection).
I am expecting the following table when all Brands are selected:
Zone | Billed Amount (Top 3 Customers) |
EAST | 188 |
NORTH | 173 |
I am expecting the following table when only BRAND-001 is selected in slicer:
Zone | Billed Amount (Top 3 Customers) |
EAST | 73 |
NORTH | 34 |
If anyone can help me with the DAX to calculate the measure it would be of great help.
Thanks.
Solved! Go to Solution.
hi @GreenP
try to plot the zone column and a measure like this:
Top3Amt =
CALCULATE(
SUM(TableName[Bill Amount]),
TOPN(
3,
ALL(TableName[Customer]),
CALCULATE(SUM(TableName[Bill Amount]))
)
)
verified and worked like this:
hi @GreenP
try to plot the zone column and a measure like this:
Top3Amt =
CALCULATE(
SUM(TableName[Bill Amount]),
TOPN(
3,
ALL(TableName[Customer]),
CALCULATE(SUM(TableName[Bill Amount]))
)
)
verified and worked like this:
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |