Measure to be visualized in bar chart as a category

[Xilitor01]

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 🙂

[SolomonovAnton]

 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 

[Xilitor01]

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].

[SolomonovAnton]

sorry, my mistake.   you should create Calculation Group in Tabular editor, not calculated table

[Xilitor01]

I am not very experienced with Tabular Editor. It is not possible to do in PBI?

[SolomonovAnton]

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 

[Xilitor01]

Thanks for your reply.

Will this count the Products within each group?
When I do it it works just like my own 'Percent group'.

[SolomonovAnton]

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