March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Expected Output similar to:
Please help me with the logic.
Sample file: https://www.dropbox.com/s/hemibrwsquy0mhu/Catogary_Sample_PBI.pbix?dl=0
Solved! Go to Solution.
@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
@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))
)
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" )
)
)
)
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
@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
Hi @amitchandak thank you for this solution, it works fine for color changes in chart.
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:
@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))
)
@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",
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
129 | |
90 | |
75 | |
58 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |