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
Sarevem
Frequent Visitor

Distinct Count - Calculate Column

I created a measure below (Latest 13 weeks buying) that tells me the number of weeks over the latest 13 weeks that an account has purchased any product. It works perfectly. Based on this measure value, I have another measure (brand loyalty) that assigns a value based on the measure result. 

 

Latest 13 Weeks Buying = calculate(distinctcount(Sale[Week #]), datesinperiod(CalendarTable[Date], LASTDATE(all(Sale[InvoiceDate])), -91, day), Sale[Quantity]>0, SupplierItem[Draft/Package]="D")

 

Brand Loyalty = if([Latest 13 Weeks Buying]>=9, "Continuous", if(and([Latest 13 Weeks Buying]<=4, [Latest 13 Weeks Buying]>0), "Rotation", if(and([Latest 13 Weeks Buying]>=5, [Latest 13 Weeks Buying]<9), "Frequent", "Non-Buying")))

 

 I can create a table in Power BI that shows me the Account Name with the Latest 13 weeks Buying and Brand Loyalty. 

 

The issue that i'm running into, is I want to be able to summarize the # of accounts by brand loyalty bucket. Because the formula is currently in a measure, when I try to get the count of accounts, it sums all acounts and has everything falling under the 'continuous' bucket. Nothing shows up for rotation, frequent, non-buying, etc.

Grouping.PNG

I tried putting my latest 13 weeks buying as a calculated column instead of measure (same syntax). Issue here is that if an account purchased 4 times in one week, it inflates the latest 13 weeks buying number. The column on the left in the below picture is the calculated column result and column on the right is the measure.

BrandLoyalty.PNG

 

Please let me know if you have any ideas on how to solve this. I've tried altering it to the below calculated column (Latest 13 weeks buying2) but it freezes my power bi workbook everytime. I know that I need to assign a buying loyalty at the row level but not entirely sure how to without running into the above issue where it is doublestating the distinct week count.

 

 Latest 13 Weeks Buying2 = calculate(distinctcount(Sale[Week #]), datesinperiod(CalendarTable[Date], lastdate(all(sale[InvoiceDate])), -91, day), Sale[Quantity]>0, SupplierItem[Draft/Package]="D", filter(all(sale), sale[AccountKey]= earlier(sale[AccountKey])))

 

1 ACCEPTED SOLUTION
Sarevem
Frequent Visitor

I was able to solve the issue I was running into by creating a new table to assign each account a measure value by using the summarize columns formula: 

 

Draft Buying =
summarizecolumns(Sale[AccountName], "13 Weeks Buying", [Latest 13 Weeks Buying], "13 Week CE", [13 Wk Draft CE], "13 Week BBL", [13 WK DRaft BBL], "13 Week Unit", [13 Wk Units], "LY 13 Week CE", [LY 13 Wk Draft CE], "LY 13 Week BBL", [LY 13 WK Draft BBL], "LY 13 Week Unit", [LY 13 Wk Units], "Total CE", [Total Draft CE])

View solution in original post

2 REPLIES 2
Sarevem
Frequent Visitor

I was able to solve the issue I was running into by creating a new table to assign each account a measure value by using the summarize columns formula: 

 

Draft Buying =
summarizecolumns(Sale[AccountName], "13 Weeks Buying", [Latest 13 Weeks Buying], "13 Week CE", [13 Wk Draft CE], "13 Week BBL", [13 WK DRaft BBL], "13 Week Unit", [13 Wk Units], "LY 13 Week CE", [LY 13 Wk Draft CE], "LY 13 Week BBL", [LY 13 WK Draft BBL], "LY 13 Week Unit", [LY 13 Wk Units], "Total CE", [Total Draft CE])

v-juanli-msft
Community Support
Community Support

Hi 
What I have understand is:
1.Two measures created by you and get the expected value, they are “Latest 13 Weeks Buying” and “Brand Loyalty”. 
2.Add a exist column “Account Key” from the dataset to a table visual along with the measure “Brand Loyalty”, want to calculate the count of “Account Key” based on the measure “Brand Loyalty”, but get results not expected.
3.instead, you try a calculated column to get something as measure “Latest 13 Weeks Buying” does, but it comes to not expected results.
 
It is not possible to achieve the expected count of “Account Key” based on the measure “Brand Loyalty”.
you could try calculated columns to achieve your goal, just replace the measure “Brand Loyalty” to a calculated column. 
If you want to create calculated columns for both previous measures, could you give more detailed information?
Which relationships among column “Account Key” and other columns you used in the previous measures?
what is the data model you used to create measures and calculated column? could you share a example dataset?
 
 
 
Best Regards
Maggie

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.