Dynamic Filtering and recalculation

[DataCruncher123]

Hi,

I am quite new to DAX. What I try to do is creating a kind of dynamic filter/slicer. After using a slicer in a report it should recalculate the values in the table. A table could look like this:

First Table:

Articles (given from source)	Requests (given from source)	Ranking (DAX formula)	Group (DAX formula)
a	15	1	1
b	12	2	1
c	11	3	2
d	9	4	2
e	6	5	3
f	5	6	3
g	2	7	4
h	2	8	4

Second Table (created from first table):

Group	total requests (slicer in report applied, excluding art. a,b,c,d)
1	6
2	5
3	2
4	2

So far everything works fine and I can create a graph that shows the % of the colum total of the sum group requests.

Lets say I dont want to include my articles a,b,c and d, but keep the # of groups I use (4). I would like to do this using a filter in the report and then the graph should still show group 1-4 but with the column total percentage not inclduing a,b,c,d. So e,f,g and h would be each its own group in this simplified example (could be many more articles).

Any ideas how to do this? Let me know if it is unclear and I can try to explain better! 🙂

BR

Moritz

[Anonymous]

Hi @DataCruncher123 ,

Sorry, not very clear about the logic of total requests.

About creating a silicer/filter, you could refer to:

Slicers in Power BI

Add a filter to a report in Power BI

Best Regards,

Stephen Tao

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

[MFelix]

Hi @DataCruncher123 ,

Can you share a little bit more information about how the calculations are being performed namely on the group part of your calculations?

How are you settting up the values for group 1 , 2, 3 ,4 ? Is it based on the values, percentages?

Are the DAX columns you are presenting measures or calculated columns? Can you share the DAX syntax?

[DataCruncher123]

Hi @MFelix  and thanks for looking into it!

I know that this will only work with measure but not sure how exactly. The Grouping works by counting the total number of rows and then split it into X amount of groups. The Group 1 should then include the rows with the highest requests. This I could solve already (see below):

However, what I would like to achieve is to be able to is to change into how many groups it should be split - In this case it is 16

[MFelix]

Hi @DataCruncher123 ,

To what I can understand you have the High Runner that is the ranking of the requests and the levels that is the bin taking into account the ranking.

On my calculation since I only have a few rows (8) I calculate to 4 groups believe you have more isnce you are divinding by 16, however you just need to adjust to the calculation has you may need.

Create a disconnected table with your groups in my case it's only 4 so I have a simple table with:

Groups = GENERATESERIES(1 ; 4; 1)

Create the following measures:

rank measure = RANKX(ALLSELECTED('Table');CALCULATE(SUM('Table'[Request])))

Group_Request = ROUND ( [rank measure] / ( COUNTROWS ( ALLSELECTED ( 'Table' ) ) / 4 ); 0 )

Requests by group =
VAR temp_table =
    SUMMARIZE (
        ALLSELECTED ( 'Table' );
        'Table'[Articles];
        'Table'[Request];
        "Rank"; [rank measure];
        "Group"; [Group_Request]
    )
RETURN
    IF (
        HASONEFILTER ( Groups[Group] );
        SUMX (
            FILTER ( temp_table; [Group] = SELECTEDVALUE ( Groups[Group] ) );
            'Table'[Request]
        );
        SUM ( 'Table'[Request] )
    )

Result below and in attach PBIX file.

[DataCruncher123]

Hi, thanks for the support @MFelix.

I think this is closer to what I need but in the end I want to be able to use a filter in my report where I can select that I want 1-X levels in total and depending on that it will recalculate how many requests come from the different levels.

The more levels I want to use (that can vary between 1-24) the less requests I have from each level. Also, the low-number-levels will have the SKUs/Articles with the highest number of requests.

I already have a solution with calculated tables and columns which allows me to do this, however I want a dynamic recalculation based on the filter context in the report.

[MFelix]

Hi @DataCruncher123 ,

What do you mean by:

---

@DataCruncher123 wrote:

The more levels I want to use (that can vary between 1-24) the less requests I have from each level. Also, the low-number-levels will have the SKUs/Articles with the highest number of requests.

Instead of having only the 4 levels you want to have 24? do you want that those levels are dynamic so if you select 10 it will calculate for 10?

Can you please elaborate a little bit better.

[DataCruncher123]

@MFelix 

yes exactly I want to select the number of levels via slicer in the report view. Depending on the number of levels it will calculate the total number per level. If it is only 1 level selected, all requests come from level 1, if it is 24 levels, the SKUs will be distributed equally to all levels while the first level has all the super high runners SKUs and the 24th level all the super low runner SKUs

[MFelix]

Hi @DataCruncher123 ,

Create a new table with the groups you need in this case the 24, you will have two table with 24 groups:

Number_Groups = GENERATESERIES(1, 24, 1)

Now redo your measures to:

Group_Request =
ROUNDUP (
    [rank measure]
        / (
            COUNTROWS ( ALLSELECTED ( 'Table' ) )
                / SELECTEDVALUE ( Number_Groups[Number_Groups] )
        ),
    0
)

rank measure = RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[Request])))


Requests by group = 
VAR temp_table =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[Articles],
        'Table'[Request],
        "Rank", [rank measure],
        "Group", [Group_Request]
    )
RETURN
    IF (
        HASONEFILTER ( Groups[Group] ),
        SUMX (
            FILTER ( temp_table, [Group] = SELECTEDVALUE ( Groups[Group] ) ),
            'Table'[Request]
        ),
        SUM ( 'Table'[Request] )
    )

Be aware that if the number of groups is lower than the number of selected values you will get erratic number of groups on the table visualization.