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!

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