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,
I have the fields 'Product' and a measure 'Percent'. I want to group the Percentage into different groups so it's easier to get an overview:
I have tried to create a measure to do that but it only does that for a specific Product. If I do a DISTINCTCOUNT on the Product it will just show the DISTINCTCOUNT of Product but not seperated into each category (Percent Group).
How can I DISTINCTCOUNT the number of Products in each Percent group? I want to visualise in a bar chart to show the number of products within each Percent Group.
NOTE: I am running data in DirectQuery so the solution has to work in DirectQuery.
I hope someone can help 🙂
if you want put you data in bar chart you should create Calculate table
like this
there script for one calculation item you can change it for the same calculation item
var _Data_Percent_table = SUMMARIZE('DataTable','DataTable'[Product],"@Percent",[Percent])
var _result_table = filter(_Data_Percent_table,[@Percent]>0.1&&[@Percent]<=0.3)
var _result = countrows(_result_table)
return _result
as a result you will get
I tried to create a calculated table with the code below but I get the following error:
Where 4_FEATURIZ_SOMMERHUSUDLEJNINGER = DataTable
Lejemål (ID) = Product
[Blokeringsprocent] is my measure for [Percent].
sorry, my mistake. you should create Calculation Group in Tabular editor, not calculated table
I am not very experienced with Tabular Editor. It is not possible to do in PBI?
one more variant of decision
a bit easier for understand
percent group2 =
var result = SWITCH(ISBLANK([Percent])=FALSE(),
[Percent]>0 && [Percent]<=0.1, "0%-10%",
[Percent]>0.1 && [Percent]<=0.3,"10%-30%",
[Percent]>0.3 && [Percent]<=0.5, "30%-50%",
[Percent]>0.5 && [Percent]<=1, "50%-100%",
"antother")
return result
you will get next result
Thanks for your reply.
Will this count the Products within each group?
When I do it it works just like my own 'Percent group'.
I hope it is what you need 🙂
you should create measure like that
Percent group =
-- Dict_percent_group it is dictionary table with bin's borders
var Dict_percent_group = DATATABLE (
"@bin", STRING,
"@min_bin", DOUBLE,
"@Max_bin", DOUBLE,
{
{ "0%-10%", 0, 0.1 },
{ "10%-30%", 0.1, 0.3 },
{ "30%-50%", 0.3, 0.5 },
{ "50%-100%", 0.5, 1 }
}
)
-- 'DataTable' - it is your table with field [Product]
var Data_Percent_table = SUMMARIZE('DataTable','DataTable'[Product],"@Percent",[Percent])
var Crossjoin_table = CROSSJOIN( Dict_percent_group, Data_Percent_table)
var result_Table = FILTER(Crossjoin_table,[@Percent]>[@min_bin] && [@Percent]<=[@Max_bin])
var final = MINX(result_Table,[@bin])
return final
you will get result as below table
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |