cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Measure for max in category without using category filter

So I have dashboard in which I rank companies in several Aspects. Each company belongs to one Industry.

What I want to do is select company in Company slicer and see on a line chart score for that company in different Aspects. Besides that I want to have another line in that chart, which would be showing Maximum Score for the industry from which I selected the company. Currently  the Maximum Score  works for me correcctly only when I select both company and industry, even though once I select company I can select only one industry as all the others are filtered out. What I want is to select company without need to select industry.

 

I defined the measure for Maximum score for industry following:

 

Max_score_industry = CALCULATE(MAX(DataTable[Score]),ALLEXCEPT(DataTable,DataTable[Aspect],DataTable[Industry]))

 

Hope I am clear enough, all data is in one simple table. Any idea how should I define my Max_score_industry measure or what else I need to change?

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Measure for max in category without using category filter

Hi,

You  may download my PBI file from here.  See the result of the measure "Max_score_industry try".  It works even without selecting the Industry.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Highlighted
Super User VII
Super User VII

Re: Measure for max in category without using category filter

@dalmar22 remove INdustry column from ALLEXCEP  function.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Frequent Visitor

Re: Measure for max in category without using category filter

Thanks for respond @parry2k , however when I do that the measure shows maximum across all the companies. What I need is to show only maximum for the industry from which I selected company. 

Highlighted
Community Support
Community Support

Re: Measure for max in category without using category filter

Hi,  @dalmar22 

Did you want to have another line in the chart?

Maybe you can try to create another Axis and apply it to  line chart visual.
For example:

 

 

Axis = UNION(DISTINCT('DataTable'[Industry]),SELECTCOLUMNS({{"0"},{"Z"}},"Industry",[Value]))

 

 

Then you need to  create the corresponding measure "Max_score_industry"  as value.

Here is  demo .   ( please ignore the Measure "Max_score_industry" in demo,it is not right. ) This demo is  just  used to better express my idea .Hope it can bring you some ideas.

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: Measure for max in category without using category filter

Hi @v-easonf-msft ,

thanks for your suggestion, I am afraid the file you attached is not the correct one, as i dont see anywhere "Max_score_industry".

Can you please check?

 

Thanks a lot.

Highlighted
Community Support
Community Support

Re: Measure for max in category without using category filter

Hi , @dalmar22 

Sorry for my mistake.Please check it here.

Here I just put forward my idea, please ignore the measure I wrote, because it is incorrect, it still needs to be modified .

 

Best Regards,
Community Support Team _ Eason

Highlighted
Super User V
Super User V

Re: Measure for max in category without using category filter

Hi,

Share a simple dataset and show the expected result in a simple Table format.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: Measure for max in category without using category filter

Hi @Ashish_Mathur @v-easonf-msft ,

please find my simplified file, desription of the problem is included. Thanks a lot for your suggestions.

 

 

Highlighted
Super User V
Super User V

Re: Measure for max in category without using category filter

Hi,

You  may download my PBI file from here.  See the result of the measure "Max_score_industry try".  It works even without selecting the Industry.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors