Measure count and median

[Lodan]

Hello,

I have a math problem and I hope you can help please.

I have a data source of customers, revenue, when it started and when it stopped etc.  One customer could be in it multiple times.  I then have an MRR measure that basically isolates the active revenue, does the exchange rate and sums it up per customer.  That works well and gives me the bar chart in the picture (measure is below).

What I am now trying to do is produce a count of the customers in that list and a median MRR.  My measure (also below) does this but gets the answer wrong (there are more than 11 customers in that list and the median value isn't correct).  Any thoughts please?

MRR Measures:

Month Total Revenue (MRR + NRR) = [MRR Current Month]+[NRR Filtered]

MRR Current Month = CALCULATE(
    [MRR Revenue],
    FILTER('Dates2','Dates2'[Year] = [AAReporting Year] && Dates2[Month Name] = [AAReporting Month]
    )
)

MRR Revenue = 
VAR BaseTable = SUMMARIZE( 'GBP', 'Dates2'[MY], 'GBP'[Source], 'GBP'[Rate] )
VAR ConvertedValueGBP = SUMX(
	BaseTable,
	VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Source = 'GBP'[Source]
	VAR Result = CALCULATE(
		SUM( Combined[MRR] ),
		Combined[End Date] > MaxDate,
        Combined[Start Date] <= MaxDate,
        Combined[PON or Not] IN { "PON", "Excluded" },
		Combined[Currency] = Source
	) * 'GBP'[Rate]
	RETURN Result
)
VAR BaseTable2 = SUMMARIZE( 'EUR', 'Dates2'[MY], 'EUR'[Source], 'EUR'[Rate] )
VAR ConvertedValueEUR = SUMX(
	BaseTable2,
	VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Source = 'EUR'[Source]
	VAR Result = CALCULATE(
		SUM( Combined[MRR]),
		Combined[End Date] > MaxDate,
        Combined[Start Date] <= MaxDate,
        Combined[PON or Not] IN { "PON", "Excluded" },
		Combined[Currency] = Source
	) * 'EUR'[Rate]
	RETURN Result
)
VAR BaseTable3 = SUMMARIZE( 'CHF', 'Dates2'[MY], 'CHF'[Source], 'CHF'[Rate] )
VAR ConvertedValueCHF = SUMX(
	BaseTable3,
	VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Source = 'CHF'[Source]
	VAR Result = CALCULATE(
		SUM( Combined[MRR]),
		Combined[End Date] > MaxDate,
        Combined[Start Date] <= MaxDate,
        Combined[PON or Not] IN { "PON", "Excluded" },
		Combined[Currency] = Source
	) * 'CHF'[Rate]
	RETURN Result
)
VAR UnconvertedValue = SUMX( VALUES( 'Dates2'[MY] ),
	VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Result = CALCULATE(
		SUM( Combined[MRR $] ),
		Combined[End Date] > MaxDate,
        Combined[Start Date] <= MaxDate,
        Combined[PON or Not] IN { "PON", "Excluded" },
		ISBLANK( Combined[Currency] ) 
	)
	RETURN Result
)
VAR Result = ConvertedValueGBP + UnconvertedValue + ConvertedValueEUR + ConvertedValueCHF
RETURN Result

Count Measure:

Client Count = 
VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
VAR Result = CALCULATE(
		DISTINCTCOUNT( Combined[Customer] ),
		Combined[End Date] > MaxDate,
        Combined[Start Date] <= MaxDate,
        Combined[PON or Not] IN { "PON", "Excluded" }
	) 
	RETURN Result

Current Customer Count = CALCULATE(
    [Client Count],
    FILTER('Dates2','Dates2'[Year] = [AAReporting Year] && Dates2[Month Name] = [AAReporting Month]
    )
)

Median measure:

Median MRR = 
VAR T1 =
    SUMMARIZE (
        Combined,
        Combined[Customer],
        "Curr MRR", [MRR Current Month]
    )
RETURN
    MEDIANX (
        T1,
        [Curr MRR]
    )

Thanks and regards

[DataNinja777 · Accepted solution]

Hi @Lodan ,

The discrepancy you're seeing in the customer count and median MRR likely comes from how context is handled within your measures. The [MRR Revenue] measure involves multiple currency conversions and filters based on start and end dates. However, in your Client Count and Median MRR measures, you're summarizing the Combined table directly, which might not align with the way [MRR Revenue] is computed in the visual. To resolve this, you can explicitly calculate [MRR Revenue] for each customer, applying the same logic and filters, and then derive both the count and median from this list of customers with actual MRR values. Here's how you can rewrite the measures:

Current Customer Count = 
VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
VAR T1 =
    ADDCOLUMNS (
        VALUES( Combined[Customer] ),
        "CustomerMRR", 
            CALCULATE(
                [MRR Revenue],
                Combined[End Date] > MaxDate,
                Combined[Start Date] <= MaxDate,
                Combined[PON or Not] IN { "PON", "Excluded" }
            )
    )
RETURN
    COUNTROWS(
        FILTER( T1, [CustomerMRR] > 0 )
    )

Median MRR = 
VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
VAR T1 =
    ADDCOLUMNS (
        VALUES( Combined[Customer] ),
        "CustomerMRR", 
            CALCULATE(
                [MRR Revenue],
                Combined[End Date] > MaxDate,
                Combined[Start Date] <= MaxDate,
                Combined[PON or Not] IN { "PON", "Excluded" }
            )
    )
RETURN
    MEDIANX (
        FILTER( T1, [CustomerMRR] > 0 ),
        [CustomerMRR]
    )

These versions ensure that only customers contributing non-zero MRR values at the selected reporting date are included in the count and median calculations, matching exactly what’s displayed in your visual.

Best regards,

[Lodan]

Thats fantastic thanks very much !

It worked a treat and I will remember that for future measures.

Best regards

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Lodan]

Hello,

Thanks for your reply.  In this case "Do not include sensitive information" negates "Please provide sample data".

Of course I could (and will if needed) create a new dataset that has the same structure but I was just wondering if anyone saw any issues with the measures please.

Thanks