Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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",
)
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
94 | |
90 | |
35 | |
35 |
User | Count |
---|---|
154 | |
100 | |
82 | |
63 | |
53 |