Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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",
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |