How can I create a dynamic measure or column for an array in PowerBI?

I am attempting to recreate a similar chart in PowerBI as I did in excel seen below:

Here I have revenue per day. The chart shows the percent of days where revenue exceeds a fixed amount (100, 200, etc).

In PowerBI I know how to recreate the table that the chart is based on by defining a column, but it's not dynamic. I can't apply filters to change the column values.

I know I can apply filters to measures but when I try to replicate the formula as a measure I get an error, which I assume is due to the formula trying to return an array of values.

Here is my formula for the fixed column version:

table2 column = countx(
   filter(
   DayRevenueTable,
   [Revenue]>Table2[DayRevenueExceeding])
   ,[Day])
   /Total

Solution

Assuming your table looks like this:

Date	Revenue
04 January 2022	102
11 January 2022	162
17 January 2022	180
02 January 2022	185
12 January 2022	203
05 January 2022	278
01 January 2022	353
16 January 2022	449
14 January 2022	500
06 January 2022	515
08 January 2022	582
10 January 2022	600
03 January 2022	618
09 January 2022	626
13 January 2022	626
15 January 2022	706
18 January 2022	765
07 January 2022	895

You need to first create a table with your fixed values. Basically is the same concept as creating a parameter.

Using that table as a reference, you can create your calculation around Fixed Values[Value].

DAX: Fixed Values

Fixed Values = GENERATESERIES(100,1000,100)

DAX: Days When Revenue Exceeds Amount

Days When Revenue Exceeds Amount =
VAR CurrentFixedValue =
    SELECTEDVALUE ( 'Fixed Values'[Value] )
VAR CountValues =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Revenue] < ( CurrentFixedValue + 100 )
    )
VAR AllValues =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLSELECTED ( 'Table' ) )
VAR Calc =
    DIVIDE ( CountValues, AllValues )
RETURN
    Calc

How can I create a dynamic measure or column for an array in PowerBI?

DAX: Fixed Values

DAX: Days When Revenue Exceeds Amount

Output