cancel
Showing results for
Did you mean:
Highlighted
Helper III

Hi All, I have two measures created to show the Top 'N' and 'Others' in terms of highest profit but when added to my table neither column is able to produce a total for the column. Is it possible to add this in anyway?

The two measures are as per below;

``````Other =
VAR TopNumber =
IF( HASONEVALUE('TopN Filter'[TopN]),VALUES('TopN Filter'[TopN]),10)
VAR Rest =
DISTINCTCOUNT(Air[Parent Customer]) - TopNumber
RETURN
IF(
HASONEVALUE(CustomerName[Parent Customer]),
IF(
VALUES(CustomerName[Parent Customer]) = "Others",
SUMX(
TOPN(
Rest,
VALUES(Air[Parent Customer]),
"Measure",'Air Measures'[Total GP Air]
),
[Measure],ASC
),
[Measure]
)
)
)``````
``````Top X =
VAR TopNumber =
IF( HASONEVALUE('TopN Filter'[TopN]),VALUES('TopN Filter'[TopN]),10)
VAR Rest =
COUNTROWS (Air) - TopNumber
RETURN
IF(
HASONEVALUE(CustomerName[Parent Customer]),
CALCULATE(
[Total GP Air],
FILTER(
Air,
Air[Parent Customer] = VALUES(CustomerName[Parent Customer])
&& CONTAINS(
TOPN(
TopNumber,
ALL(CustomerName[Parent Customer]),
"Gross Profit", CALCULATE(
[Total GP Air],
FILTER(Air,Air[Parent Customer] = EARLIER(CustomerName[Parent Customer]))
)
),
[Gross Profit],DESC
),
CustomerName[Parent Customer],VALUES(CustomerName[Parent Customer])
))))``````

With the resulting table currently looking like this;

3 REPLIES 3
Highlighted
Community Champion

@Gazsim44  can you try uploading the photo of table again, I can't see it.

What is the data type for the two measures? I haven't spent much time reading your full post to know if that's the problem, but that's one of the first things I'd check.

______________

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

Highlighted
Super User IV

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Helper III

@AllisonKennedyBoth are set to whole number so I would hope this shouldnt be an issue?

Attached a copy of the table again.

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors