cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Add Total to Column in ADDCOLUMNS Measure

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,
                    ADDCOLUMNS(
                        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,
                        ADDCOLUMNS(
                            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
Community Champion

Re: Add Total to Column in ADDCOLUMNS Measure

@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. 


If you found this post helpful, please give Kudos.


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


https://sites.google.com/site/allisonkennedycv

Highlighted
Super User IV
Super User IV

Re: Add Total to Column in ADDCOLUMNS Measure

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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper III
Helper III

Re: Add Total to Column in ADDCOLUMNS Measure

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

 

Attached a copy of the table again.

 

Capture.PNG

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

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

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