Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shower999
Frequent Visitor

tricky measurement [this quarter numerator over last quarter denominator]

All expert,

 

I have an easy metrics but I couldn't resolve it in hour

I have a table with these 4 fields

 

[Accts_Status] = Text, "Attrited_Accts" and etc
[Accts_with_Asset] = 1 or 0
[EOQ_Date].[Date] = Date

 

basically I need to use a measurement to divide 'count of "attrited_Accts" over last quarter' 'sum of [Accts_with_Asset]

for an example, I have below aggregated data

 

sum of [Accts_with_Asset] by Data_Qtr

FY19Q1  116,019

FY19Q2  121,609

FY19Q3  128,840

 

Count of [Accts_Status] filter in "Attrited_Accts" by Data_Qtr

FY19Q1  2,842

FY19Q2  5260

FY19Q3  4,774

 

My expected results is

FY19Q1  null

FY19Q2  4.5% (5260 / 116019)

FY19Q3  3.9% (4774 / 121609)

 

for some reason, the measurement only show me below which is not my expectation

 

FY19Q1  2.45% 

FY19Q2  4.33% (5260 / 121609)

FY19Q3

 

here is the code I use

 

 

Account_Attrition_rate = 
IF(
	ISFILTERED('PowerBI_RenewalReport'[EOQ_Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __Attrited_Accts =
    CALCULATE(
    	COUNTA('PowerBI_RenewalReport'[Accts_Status]),
    	'PowerBI_RenewalReport'[Accts_Status] IN { "Attrited_Accts" }
    )
VAR __PREV_QUARTER =
		CALCULATE(
			SUM('PowerBI_RenewalReport'[Accts_with_asset]),
			DATEADD('PowerBI_RenewalReport'[EOQ_Date].[Date], -1, QUARTER)
		)
RETURN
    FORMAT(DIVIDE(
        __Attrited_Accts,
        __PREV_QUARTER)
        ,"0.00%")
)

 

1 ACCEPTED SOLUTION
shower999
Frequent Visitor

hi

 

i want to thanks @v-piga-msft for the learning.

I manage to use my own measure to resolve this easily.

here is the measure I used without indexing. I don't know why it didn't work at first place. but it's working no

 

Measure = 
IF(
	ISFILTERED('Report'[EOQ_Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __PREV_QUARTER =
		CALCULATE(
			SUM('Report'[Accts_with_Asset]),
			DATEADD('Report'[Date].[Date], -1, QUARTER)
		)
	RETURN
		DIVIDE(
			SUM('Report'[Accts_with_Asset])
				- __PREV_QUARTER,
			__PREV_QUARTER
		)
)

View solution in original post

4 REPLIES 4
shower999
Frequent Visitor

hi

 

i want to thanks @v-piga-msft for the learning.

I manage to use my own measure to resolve this easily.

here is the measure I used without indexing. I don't know why it didn't work at first place. but it's working no

 

Measure = 
IF(
	ISFILTERED('Report'[EOQ_Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __PREV_QUARTER =
		CALCULATE(
			SUM('Report'[Accts_with_Asset]),
			DATEADD('Report'[Date].[Date], -1, QUARTER)
		)
	RETURN
		DIVIDE(
			SUM('Report'[Accts_with_Asset])
				- __PREV_QUARTER,
			__PREV_QUARTER
		)
)
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @shower999,

 

If the sum of [Accts_with_Asset] and Count of [Accts_Status] are columns, you could follow the steps below.

 

1. Create the Index column in Query Editor.

2. Create the calculated column with the formula below.

 

 

Column =
CALCULATE (
MAX ( Table1[Accts_with_Asset] ),
FILTER ( 'Table1', 'Table1'[Index] < EARLIER ( 'Table1'[Index] ) )
)

 

3. Create the measure below.

 

Measure = DIVIDE(MAX('Table1'[Accts_Status]),MAX('Table1'[Column]))

 

 

Here is the output.

 

Capture.PNG

 

 

If you still need help, could you share your data sample as table format and your desired output so that I could understand your requirement better and get the solution.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hi, @v-piga-msft 

 

I'm still stuck at step 2.

now I got this error says 'there is not enough memory to complete this operation'

is there any other work around that need less memory?

hi, @v-piga-msft

 

I sorted it with acsending to ensure the index works

 

but I am not able to get through step 2.

it says EARLIER/EARLIEST refers to an earlier row context which does not exist

 

I think that maybe because the [Accts_with_asset] data are all 0 because it supposed to be 0 instead of 1 for this [Accts_Status]

so it won't work with the max.

 

here are some sample of the data

 

Data_Qtr	account_id	Accts_Status	Accts_with_Asset
FY18Q1		496		Attrited_Accts			0
FY18Q1		1094		Attrited_Accts			0
FY18Q1		1343		Attrited_Accts			0
FY18Q1		1447		Attrited_Accts			0
FY18Q1		1575		Attrited_Accts			0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.