Create a dimnesion based on a field to categorize other measures

[Manudata]

Hello, I am trying to make a visualization and I am unable to find the way

I explain myself with a simple model, where I have a fact table with records of picking harvest jobs, a dimension table with the fruit plantation where the collection was donde and another table with the owners of this plantations.

The field I want to create is a size range field, so that I can make a visualization that tells me for a certain size range, when there are fields, how much has been collected in them.

At the same time, this range field should function as a filter, so that if I click on the range 100-200, in a table where I have the list of plantations, it will tell me which ones they are.

An additional complication is that if I filter for example idfruits, the measurements inside must be dynamic (example with no. owners:  After filtering certain idfruits, one owner that I am counting in a range of 1000-2000 when removing certain plantations, I must count him in 100-500 range)

[Jihwan_Kim]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

One of ways is having the range_surface table like below.

Quantity total: = 
VAR _rangetablecondition =
    FILTER (
        d_plantation,
        CALCULATE ( SUM ( d_plantation[surface_size] ) ) >= MIN ( d_rangesurface[min] )
            && CALCULATE ( SUM ( d_plantation[surface_size] ) ) <= MAX ( d_rangesurface[max] )
    )
RETURN
    CALCULATE ( SUM ( f_harvest[quantity] ), _rangetablecondition )

# plantation: = 
VAR _rangetablecondition =
    FILTER (
        d_plantation,
        CALCULATE ( SUM ( d_plantation[surface_size] ) ) >= MIN ( d_rangesurface[min] )
            && CALCULATE ( SUM ( d_plantation[surface_size] ) ) <= MAX ( d_rangesurface[max] )
    )
RETURN
    CALCULATE ( COUNTROWS(DISTINCT(d_plantation[idplantation])), _rangetablecondition )

# manager: = 
VAR _rangetablecondition =
    FILTER (
        d_plantation,
        CALCULATE ( SUM ( d_plantation[surface_size] ) ) >= MIN ( d_rangesurface[min] )
            && CALCULATE ( SUM ( d_plantation[surface_size] ) ) <= MAX ( d_rangesurface[max] )
    )
RETURN
    CALCULATE ( COUNTROWS(DISTINCT(d_plantation[idmanager]) ), _rangetablecondition )

[talespin]

Hi @Manudata ,

I think this is the solution you need. Create groups in Power BI or Power Query, there are multiple solutions shown here.

Create Buckets or Groups with Power Query in Power BI - YouTube

[v-jialongy-msft]

Hi @Manudata 

Can you provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.