cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
67nmark Regular Visitor
Regular Visitor

measure to calculate highest value from grouped total

Hello,

 

I have a dataset with a column called CityID and a measure called % of total sales MEASURE. The visual looks like this:

dax.png

 

 

 

 

 

 

 

 

 

 

 

 

I'm trying to create two measures (and having zero success) showing the top selling city (1) and top percentage (29%).

 

Very grateful to anyone who can help with this?

 

Cheers,

Mark

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: measure to calculate highest value from grouped total

Hi Mark,

 

You can use measures like this:

Top Selling City =
FIRSTNONBLANK (
    TOPN (
        1,
        VALUES ( YourTable[CityID] ),
        [% of total sales MEASURE]
    ),
    1
)
Top Percentage =
MAXX (
    VALUES ( YourTable[CityID] ),
    [% of total sales MEASURE]
)

(see this article for the first measure)

 

The above measures are subject to any filters on CityID. You could replace VALUES with ALL or ALLSELECTED in order to give a sensible result within a visual that includes the CityID dimension.

 

Regards,

Owen



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

Proud to be a Datanaut!




5 REPLIES 5
OwenAuger Super Contributor
Super Contributor

Re: measure to calculate highest value from grouped total

Hi Mark,

 

You can use measures like this:

Top Selling City =
FIRSTNONBLANK (
    TOPN (
        1,
        VALUES ( YourTable[CityID] ),
        [% of total sales MEASURE]
    ),
    1
)
Top Percentage =
MAXX (
    VALUES ( YourTable[CityID] ),
    [% of total sales MEASURE]
)

(see this article for the first measure)

 

The above measures are subject to any filters on CityID. You could replace VALUES with ALL or ALLSELECTED in order to give a sensible result within a visual that includes the CityID dimension.

 

Regards,

Owen



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

Proud to be a Datanaut!




Matty Regular Visitor
Regular Visitor

Re: measure to calculate highest value from grouped total

Hi Owen,

 

Hope you're doing OK. Smiley Happy

 

A couple of questions regarding the solution offered:

 

  • Why have you used FIRSTNONBLANK in the first measure?  What does this account for?
  • What happens if two cities share the exact same percentage?  Will both be shown?  And what if we only wanted to show the first city, sorted ascending based on CityID?

 

Apologies for butting in on the thread - I'm trying to build up my knowledge of DAX, hence the questions...

 

Cheers,

 

Matty

OwenAuger Super Contributor
Super Contributor

Re: measure to calculate highest value from grouped total

Hi Matty,

 

I'm fine thanks - hope you're well Smiley Happy

 

  • FIRSTNONBLANK is used as a tie-breaker (technique taken from here). If there were a tie for the top CityID, the TOPN function itself would return all of them (as a single-column table). Without FIRSTNONBLANK this would cause an error (as a measure can't return a table apart from the special case of 1-row/1-column). So FIRSTNONBLANK is used to return just the first value (alphabetically or numerically).
    Actually, since MINX/MAXX can be used with Strings now, another way of writing the tie-breaker measure is:
    Top Selling City =
    MINX (
        TOPN ( 1, VALUES ( YourTable[CityID] ), [% of total sales MEASURE] ),
        YourTable[CityID]
    )
  • With the measure as I've written it, only the first CityID numerically (ascending) would be shown, so it is already doing what you've suggested.
    One way to return multiple tied CityID values would be to use CONCATENATEX:
    Top Selling City =
    CONCATENATEX (
        TOPN ( 1, VALUES ( YourTable[CityID] ), [% of total sales MEASURE] ),
        YourTable[CityID],
        ", ",
        YourTable[CityID]
    )

Cheers

Owen

 



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

Proud to be a Datanaut!




67nmark Regular Visitor
Regular Visitor

Re: measure to calculate highest value from grouped total

Hey Owen,

Thanks so much, that's exactly what I needed.

Cheers,

Mark

Matty Regular Visitor
Regular Visitor

Re: measure to calculate highest value from grouped total

Thanks Owen.  Explanation very clear.

 

Cheers,

 

Matty

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 3,064 guests
Please welcome our newest community members: