topic Power BI Dynamic Grouping in Report Server

Power BI Dynamic Grouping

Anonymous — Tue, 06 Aug 2019 23:27:51 GMT

I've a requirement on a Table to summarise the data based on below scenarios: The Table 1 is the existing, the Table 2 is what is expecting.

Table 1

Item#	ItemName	Reason	Catogery	LostSales
28783	Item 1	Orders waiting	cat1	$54
28783	Item 1	Short Term Out Of Stock	cat1	$13
28844	Item 2	Long Term Out of Stock	cat 2	$78
29193	Item 3	Orders waiting	cat 3	$15
30151	Item 4	Discontinue	cat 4	$302
30311	Item 5	Not applicable	cat 5	$64
30663	Item 6	Orders waiting	cat 6	$418

Table 2

Item#	ItemName	Reason	Catogery	LostSales
28783	Item 1	Mixed	cat1	$68
28844	Item 2	Long Term Out of Stock	cat 2	$78
29193	Item 3	Orders waiting	cat 3	$15
30151	Item 4	Discontinue	cat 4	$302
30311	Item 5	Not applicable	cat 5	$64
30663	Item 6	Orders waiting	cat 6	$418

Any one help on this achieving the Table 2.

Re: Power BI Dynamic Grouping

d_gosbell — Wed, 07 Aug 2019 04:56:01 GMT

The only way I can think of for doing this would be to create a measure which you would use in place of the "Reason" attribute.

eg.

Reason Text = SELECTEDVALUE( 'Table 1'[Reason], "Mixed")

Re: Power BI Dynamic Grouping

saykasku — Wed, 07 Aug 2019 08:11:11 GMT

Hi,

You need to create measure like this:

Reason_new = 
var _Reason = LASTNONBLANK(Arkusz1[Reason];1)
RETURN
IF(
    CALCULATE( DISTINCTCOUNT(Arkusz1[Reason]) ;
    all(Arkusz1[Reason])) > 1
    ;"Mixed"
    ;_Reason
    )

This is solution for you.

Re: Power BI Dynamic Grouping

d_gosbell — Thu, 08 Aug 2019 05:50:34 GMT

I'm not sure what behaviour you want when filters are applied via slicers that result in only 1 reason being present, but just note that @saykasku 's approach will always return "Mixed" regardless of any current filters, while the approach using SELECTEDVALUE() function will switch to showing the single reason if the current filter context eliminates the multiple reasons. Both approaches are valid, it just depends on what your requirements are.

Re: Power BI Dynamic Grouping

Anonymous — Wed, 07 Jun 2023 15:04:32 GMT

Is there some way to have something like this that will instead solo out Columns along with their supporting rows? I also need a way to group 4 columns without changing the data just want power BI to recognize that these 4 columns are all under 1 main name