Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started

Reply
Maggi029
Helper II
Helper II

How to plot maximum/minimum value plot for selected item's category

 

Table 1:

Date

Item

Sales Amount

10/01/2021

Apple

300

10/02/2022

Orange

700

20/03/2022

Carrot

300

21/03/2022

Onion

800

 

Table 2:

 

Date

Item

Category

Sales Amount

10/01/2021

Apple

Fruits

300

20/2/2022

Carrot

Vegetable

800

10/02/2022

Orange

Fruits

700

21/03/2022

Onion

Vegetable

200

 

 

Relationship: Table1[Item] and Table2[Item] are connect using Many to many relationship.

 

Im trying to plot three lines of graph for table 1.

 

Line 1- Represents the maximum sale amount based on its category(category value retrieved from selected value of item displayed in slicer)

 

Line 2 – Represents the sale amount of selected item over the date.

 

Line 3 -- Represents the minimum sale amount based on its category(category value retrieved from selected value of item displayed in slicer)

 

 

My Problem is

 

Unable to plot the maximum/minimum sale amount line based on the slicer item category.

 

When you change the item in the slicer, the maximum/minimum line chart must be updated based on the selected item category, but in this case, the lines change based on the selected item value rather than the max/min value.

 

My Question is:

 

How to plot the maximum/minimum value chart only based selected item’s category.

 

Example :

 

If I choose the apple in slicer, the maximum/minimum plot will show the maximum and minimum value of the fruits category.

 

Maggi029_0-1657542696785.png

@amitchandak  could you please look into this

2 ACCEPTED SOLUTIONS

Try the following change:

 

Maximum =
VAR CategorySelection =
    VALUES( TableDimension[Category] )
RETURN
    CALCULATE (
        MAX ( Table[Sales] ),
        FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )
    )

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Hi @Maggi029 ,

 

Try to change the metric of the max/min to somwething similar to this:

Maximum =
VAR CategorySelection =
    VALUES( TableDimension[Category] )

var ItemValues =   CALCULATE (
        DISTINCTCOUNT ( Table[Item] ),
        FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )
    )

RETURN
IF(ItemValues > 1,
    CALCULATE (
        MAX ( Table[Sales] ),
        FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )
    )
)

This should return blank for the maximum and minimum when there is only a single value so the labels will not appear.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

15 REPLIES 15
Maggi029
Helper II
Helper II

@MFelix 

I need your suggestion for below query , which is also related to this same data table 
How to find the maximum value of a measure in tabl... - Microsoft Power BI Community


MFelix
Super User
Super User

Hi @Maggi029 ,

 

I believe that you need to create two dimension tables onbe for dates and another for the items to make your relationships between the tables. 

 

However I have one question you have sales on both tables what is the sales column you want to see on the chart? Is it the values from table1, table 2 or both?


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks for your response,

 

I have made the releationship as suggested, but the real problem here how to wrote measure to plote the max/min for selected Item Category.


For your question:
It is just a replicate of original data, so table 2 has separate reports in my project

Hi @Maggi029 ,

 

If the values are the same and you create a dimension table with the items/Category then you can create a similar metric to have the maximum or minimum:

 

Maximum =
VAR CategorySelection =
    SELECTEDVALUE ( TableDimension[Category] )
RETURN
    CALCULATE (
        MAX ( Table[Sales] ),
        FILTER ( ALL ( TableDimension ), TableDimension[Category] = CategorySelection )
    )

 

Be aware that I'm doing this by heart.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks for response, You measure is  working fine, but I have one special case 

 

Case : Because one of the items belongs to two categories, "SELECTEDVALUE" does not return the expected value because it has two values.

 

Can suggest how to handle above case?

Try the following change:

 

Maximum =
VAR CategorySelection =
    VALUES( TableDimension[Category] )
RETURN
    CALCULATE (
        MAX ( Table[Sales] ),
        FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )
    )

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks for for you help, your solution working fine. 

Also i have one more query related to this, if a category has only one value, the Maximum and Minimum data points will be the same, making the group look crowded with the same value repeated when Data label is enabled. How can this be limited in this case by showing only one data label?

 

Maggi029_0-1657734507575.png

 

Hi @Maggi029 ,

 

Try to change the metric of the max/min to somwething similar to this:

Maximum =
VAR CategorySelection =
    VALUES( TableDimension[Category] )

var ItemValues =   CALCULATE (
        DISTINCTCOUNT ( Table[Item] ),
        FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )
    )

RETURN
IF(ItemValues > 1,
    CALCULATE (
        MAX ( Table[Sales] ),
        FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )
    )
)

This should return blank for the maximum and minimum when there is only a single value so the labels will not appear.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix 

 

How to find the Item name which has max value in above measure?


Note: Above measure returen maximum item values but i need item name.

Hi @MFelix 

How to find the category name here?

Hi @Maggi029 ,

 

What do you mean find the category name?


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix 

I mean , below code,  filter the  values in categoryselection

FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )

 

i need to get the selected value name when using above filter

 

Hi @MFelix 

Any update on this?

Thanks

Hi @Maggi029 

 

You want to get the values of the selection of the Categories that are selected?

 

Try the following:

Selected Categories =

CONCATANEX ( VALUES( TableDimension[Category] ),  TableDimension[Category] , ",")

 

this will concatenate the selected values into a list with comma has separator.

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix  Can you please look into this and provide your suggestion for this
How to find the maximum value of a measure in tabl... - Microsoft Power BI Community

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.