Display a new Table/Matrix/Rows Subtotals based on filtered data

[SeungLee95]

Hello,

I have this following table:

And created a matrix with the following Rows and Values/Columns based on that table:

Name	Forest	UnitStage	CostLive	Date#1	Date#2	Date#3
ABCDEFG	ABCDEFG	ABCDEFG	15.25	xx/xx/xx	yy/yy/yy	zz/zz/zz
ABCD	ABCD	ABCD	16.25	x2/x2/x2	y2/y2/y2	z2/z2/z2

Example

As seen on the table above, I also have certain filters to it. I'd like to create a new table/matrix/visual at the right of those filters. To give some more details:

My goal is to create a new table/Matrix/visual that would have some new measures called "PX" like the following:

P30	P50	P70	P100
3	5	7	10

The issue I'm running into is trying to create this new table/value based on the "Filtered out rows and their CostLive Values" from the previous matrix. This is so that every time it shows filtered out values, it can dynamically calculate those values based on what's been filtered. Is there any way to achieve this? If so, what would be the best way to achieve this as well? 

Thank you for anyone that could help me with this!

[rajendraongole1]

Hi @SeungLee95 - Yes, you can create dynamic percentile-based values (P30, P50, P70, P100) like the "PX" values you're describing Power BI. The key is to create DAX measures that respect all filters and calculate the desired percentile values from the CostLive column.

can you check with funtion, PERCENTILEX.INC is filter-aware, so it will only calculate percentiles.

PERCENTILEX.INC function (DAX) - DAX | Microsoft Learn

Please check with this and hope this works.

[SeungLee95]

Hi @rajendraongole1 ,

Thank you very much for the answer. I'm aware I can use perecentilex functions. However, my question is more so about the table/matrix. Is it possible to create a new table/matrix based on what has been filtered? And what would be the best way of going about that?

[v-venuppu · Accepted solution]

Hi @SeungLee95 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @rajendraongole1 for the prompt response.

1.It is  possible to create a new table:

• In Power BI, when you apply filters, the data in your visual is automatically affected. To create a table that only shows the filtered rows (based on the current filters), you can use a DAX function like FILTER to dynamically capture those filtered rows.

FilteredCostTable =
FILTER(
YourOriginalTable,
NOT(ISBLANK(YourOriginalTable[CostLive]))
&& YourOriginalTable[CostLive] <> 0
)

This table will dynamically adjust based on the filters applied in your visuals (such as by name, date, or other fields).

2.Now, you can create measures to calculate the different percentiles (P30, P50, P70, P100) for the CostLive values in the filtered table. Use PERCENTILEX.INC

P30 =
PERCENTILEX.INC(
FilteredCostTable,
FilteredCostTable[CostLive],
0.30
)

P50 =
PERCENTILEX.INC(
FilteredCostTable,
FilteredCostTable[CostLive],
0.50
)

P70 =
PERCENTILEX.INC(
FilteredCostTable,
FilteredCostTable[CostLive],
0.70
)

P100 =
PERCENTILEX.INC(
FilteredCostTable,
FilteredCostTable[CostLive],
1.00
)

• PERCENTILEX.INC will calculate the inclusive percentile, meaning it will calculate the exact value that corresponds to the given percentile (e.g., P30 for 30%).
• The 0.30, 0.50, 0.70, and 1.00 are the percentiles (P30, P50, P70, P100).

After defining these measures, you can add them to your matrix as columns. Every time a user applies filters, these percentiles will automatically recalculate based on the filtered rows.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.