cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vin26
Resolver I
Resolver I

Dynamic category on slicer section

Hello,

 

Please help me in creating a creating catogaries based on the mid point selections. For below scatter cart X and Y axis are calculated based on the slicers in left.

 

If the Midpoint1 = 140 and Midpoint2 = 120, all the dots in chart:

between 0 to 140 in X axis and 0 to 120 in Y axis will be Category A

Above 140 in X Axis and 0 to 120 in Y Axis will be Category B

Above 140 in X Axis and above 120 in Y axis will be Category C

0 to 140 in X Axis and Above 120 in Y axis will be Category D

 

Sample_pbi1.JPG

 

Expected Output similar to:

Sample_pbi2.JPG

 

Please help me with the logic. 

Sample file: https://www.dropbox.com/s/hemibrwsquy0mhu/Catogary_Sample_PBI.pbix?dl=0 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@vin26 , you have to create a measure like this example measure and use that in conditional formatting

Color Dot = 
	var _avgDisc = CALCULATE([Discount %],ALL(Retail))
	var _avgMargin = CALCULATE([Margin %],ALL(Retail))
	return switch(TRUE(),
	[Margin %]>_avgMargin && [Discount %] <_avgDisc , "Green",
	[Margin %]>_avgMargin && [Discount %] >_avgDisc , "Blue",
	[Margin %]<_avgMargin && [Discount %] <_avgDisc , "Yellow",
	[Margin %]<_avgMargin && [Discount %] >_avgDisc , "Red", "Black")

 

I have explained how to do it in this webinar

https://youtu.be/Q1vPWmfI25o?t=2851

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

View solution in original post

@vin26 - I think it is because of your logical expressions, perhaps:

Measure = 
    var _avgDisc = CALCULATE('Midpoint1_X Axis'[Midpoint1 Value],ALL(Data))
	var _avgMargin = CALCULATE('Midpoint2_Y Axis'[Midpoint2 Value],ALL(Data))
        return 
            switch(MAX('Table'[Category]),
                "A",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] <_avgDisc)),
                "B",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] >_avgDisc)),
                "C",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] <_avgDisc)),
                "D",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] >_avgDisc))
            )

If not, maybe try eliminating some CALCULATE statements:

Measure = 
    var _avgDisc = CALCULATE('Midpoint1_X Axis'[Midpoint1 Value],ALL(Data))
	var _avgMargin = CALCULATE('Midpoint2_Y Axis'[Midpoint2 Value],ALL(Data))
        return 
            switch(MAX('Table'[Category]),
                "A",COUNTROWS(FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] <_avgDisc)),
                "B",COUNTROWS(FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] >_avgDisc)),
                "C",COUNTROWS(FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] <_avgDisc)),
                "D",COUNTROWS(FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] >_avgDisc))
            )

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

6 REPLIES 6
Eyelyn9
Community Support
Community Support

Hi @vin26 ,

As @Greg_Deckler said ,you could use SWITCH() or use IF()function like this:

categoryType =
IF (
    [forX] >= 0
        && [forX] <= 140
        && [forY] >= 0
        && [forY] <= 120,
    "Categoty A",
    IF (
        [forX] > 140
            && [forY] >= 0
            && [forY] >= 0
            && [forY] <= 120,
        "Category B",
        IF (
            [forX] > 140
                && [forY] > 120,
            "Category C",
            IF ( [forX] >= 0 && [forX] <= 140 && [forY] > 120, "Category D" )
        )
    )
)

9.4.3.1.png

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,

Eyelyn Qin

amitchandak
Super User
Super User

@vin26 , you have to create a measure like this example measure and use that in conditional formatting

Color Dot = 
	var _avgDisc = CALCULATE([Discount %],ALL(Retail))
	var _avgMargin = CALCULATE([Margin %],ALL(Retail))
	return switch(TRUE(),
	[Margin %]>_avgMargin && [Discount %] <_avgDisc , "Green",
	[Margin %]>_avgMargin && [Discount %] >_avgDisc , "Blue",
	[Margin %]<_avgMargin && [Discount %] <_avgDisc , "Yellow",
	[Margin %]<_avgMargin && [Discount %] >_avgDisc , "Red", "Black")

 

I have explained how to do it in this webinar

https://youtu.be/Q1vPWmfI25o?t=2851

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Hi @amitchandak thank you for this solution, it works fine for color changes in chart.

 

PBI2.JPG

 

 

I would also require group the values by category, for that I have created a table with categories and created a measure like below:

Measure = 
    var _avgDisc = CALCULATE('Midpoint1_X Axis'[Midpoint1 Value],ALL(Data))
	var _avgMargin = CALCULATE('Midpoint2_Y Axis'[Midpoint2 Value],ALL(Data))
        return 
            switch(MAX('Table'[Category]),
                "A",CALCULATE(COUNT(Data[Sl No]),[Measure_Value1]>_avgMargin && [Measure_Value2] <_avgDisc),
                "B",CALCULATE(COUNT(Data[Sl No]),[Measure_Value1]>_avgMargin && [Measure_Value2] >_avgDisc),
                "C",CALCULATE(COUNT(Data[Sl No]),[Measure_Value1]<_avgMargin && [Measure_Value2] <_avgDisc),
                "D",CALCULATE(COUNT(Data[Sl No]),[Measure_Value1]<_avgMargin && [Measure_Value2] >_avgDisc)
            )

it is not working and getting below error:

pbi_error.JPG 

@vin26 - I think it is because of your logical expressions, perhaps:

Measure = 
    var _avgDisc = CALCULATE('Midpoint1_X Axis'[Midpoint1 Value],ALL(Data))
	var _avgMargin = CALCULATE('Midpoint2_Y Axis'[Midpoint2 Value],ALL(Data))
        return 
            switch(MAX('Table'[Category]),
                "A",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] <_avgDisc)),
                "B",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] >_avgDisc)),
                "C",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] <_avgDisc)),
                "D",CALCULATE(COUNT(Data[Sl No]),FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] >_avgDisc))
            )

If not, maybe try eliminating some CALCULATE statements:

Measure = 
    var _avgDisc = CALCULATE('Midpoint1_X Axis'[Midpoint1 Value],ALL(Data))
	var _avgMargin = CALCULATE('Midpoint2_Y Axis'[Midpoint2 Value],ALL(Data))
        return 
            switch(MAX('Table'[Category]),
                "A",COUNTROWS(FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] <_avgDisc)),
                "B",COUNTROWS(FILTER(Data,[Measure_Value1]>_avgMargin && [Measure_Value2] >_avgDisc)),
                "C",COUNTROWS(FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] <_avgDisc)),
                "D",COUNTROWS(FILTER(Data,[Measure_Value1]<_avgMargin && [Measure_Value2] >_avgDisc))
            )

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Hi @Greg_Deckler Perfect!, thanks a ton, both works!

Greg_Deckler
Super User
Super User

@vin26 - Seems like you could create a column like:

Column =
  VAR __MidX = <calculate mid>
  VAR __MidY = <calculate mid>
RETURN
  SWITCH(TRUE(),
    <logical criteria 1>,"Category 1",
    <logical criteria 2>,"Category 2",
    <logical criteria 3>,"Category 3",
    <logical criteria 4>,"Category 4",
  )

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!