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.
Hi All,
I am trying hard to find a way to achieve my desired result.
In this group,
Supplier A: occur 4 times
Supplier C: occur 1 time
Supplier E: occur 1 time
I am hoping the Sub-total can show the most frequent supplier, which means "A"
Please refer to the Sample BI
Since "Supplier 1" itself is a measure, I know it will be a little tricky.
Appreciated if I could get any help from here.
For more information, what I am trying to do is to create a table to show the users which suppliers with the lowest cost across all suppliers for each QTY .
Solved! Go to Solution.
Hi, @ngct1112
1. To create a calculated table:
subT =
var _t1=SUMMARIZE('Table',[Quantity From],"Su",[Supplier 1])
return _t1
2. To create 5 measures
__isP1 = ISFILTERED('Table'[Product Type])
__isP2 = ISFILTERED('Table'[Quantity From])
P1+P2 = [__isP1]+[__isP2]
__maxCountSu =
var _t=MAXX(TOPN(1,SUMMARIZE('subT',[Su],"count",COUNTX('subT',[Su])),[count]),[Su])
return _t
__Supplier 1.1 =
SWITCH(
[P1+P2],
1,[__maxCountSu],[Supplier 1])
Then the result would be like this:
Please refer to the attachment below for details
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @ngct1112
1. To create a calculated table:
subT =
var _t1=SUMMARIZE('Table',[Quantity From],"Su",[Supplier 1])
return _t1
2. To create 5 measures
__isP1 = ISFILTERED('Table'[Product Type])
__isP2 = ISFILTERED('Table'[Quantity From])
P1+P2 = [__isP1]+[__isP2]
__maxCountSu =
var _t=MAXX(TOPN(1,SUMMARIZE('subT',[Su],"count",COUNTX('subT',[Su])),[count]),[Su])
return _t
__Supplier 1.1 =
SWITCH(
[P1+P2],
1,[__maxCountSu],[Supplier 1])
Then the result would be like this:
Please refer to the attachment below for details
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
though totally have no idea how it happens, it works fine in the model.
Great thanks for your brillant solution.
@ngct1112 , Try TOPN, I am assuming it should work at sub total level too
CALCULATE(max(Data[Supplier]),TOPN(1,allselected(Data[Supplier]),[Supplier cost],DESC),VALUES(Data[Supplier]))
Hi @amitchandak , I tried to apply this formual but not working. Attached as Sample BI
Please be reminded that
1. "Unit Price_FX" itself is a meaure.
2. "Supplier 1" is referring to the lowest cost supplier, filtered by a "Rank = 1" measure
Rank by Supplier =
RANKX(
FILTER(
ALLSELECTED('Table'[Group],'Table'[Supplier]),
'Table'[Group]=MAX('Table'[Group])),
[UnitPrice_FX],,ASC)
Appreciated if there is any solution.
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 |