Bucket Sales Managers by their performance for a specific time frame
Hi, I am trying to male a report with buckets for our managers according to the performance they had last quarter. The goal is to make a matrix where I can report the number of managers belonging to each group according to how many leads they generated. To do this, I started by setting up a new table which looks like this: After that I added a measure counting the number of unique leads: Unique Leads = DISTINCTCOUNT(Leads_Table[DimLeadId]) Thirdly, I added a measure with filter and values functions: Leads Group = CALCULATE([Unique Leads], FILTER(VALUES(Leads_Table[LeadRecordId__c]), COUNTROWS( FILTER('Bucket Table', [Count Leads] >= 'Bucket Table'[Lower Bucket] && [Count Leads] <= 'Bucket Table'[Upper Bucket])) > 0)) I tried using this measure in a matrix and got no results. There is something here that I really don't understand as to how create a segmentation based on a measure. Can anybody check this logic and explain how to write the measure correctly?
How to find average cost spent within a category per quarter/year
Hi there, I have a report that analyzes the amount spent on third-party vendors over the last few years. There are 26 different categories the vendors can fall under. I am trying to determine the average amount spent within a category per quarter/year and display it as a secondary Y-axis on a line chart. The line chart currently displays the total amount spent on the vendor per quarter by the year the user selects in the slicer visual. This is for a drill through page, so the user will select a vendor on the report page, which will then take them to the vendor's details on the drill through page. So the secondary Y-axis would show the average cost of the category of the selected vendor per quarter. My question is, is it possible to create a measure to find the average cost spent within a category per quarter? If so, how would I do it? It's tricky because I have to group the categories and then determine the number of vendors within each category for that quarter in order to find the average amount spent (I think this is called segmentation?). I'm just not sure if this can be done by a measure or if I need to create a separate data table to figure this out. Below is the data table I would use to pull information from, which includes sample data. The actual data table includes all of the invoices for each vendor throughout the month starting from Q1 2019 to Q1 2022. So just imagine that each vendor would have a lot more rows of invoices for each month/year. Vendor Name Vendor Category Invoice Number Invoice Description Invoice Date Invoice Amount Vendor 1 Category C 1447 Description 1/1/2019 $234.97 Vendor 1 Category C 3457 Description 2/1/2019 $459.03 Vendor 2 Category B 123 Description 1/1/2019 $3605.99 Vendor 2 Category B 654 Description 2/1/2019 $4526.86 Vendor 3 Category A 28998 Description 1/1/2019 $39408.33 Vendor 3 Category A 12879 Description 2/1/2019 $23609.76 Vendor 4 Category B 1257 Description 1/1/2019 $1094.32 Vendor 4 Category B 2146 Description 2/1/2019 $2094.31 Vendor 5 Category C 4948 Description 1/1/2019 $609.71 Vendor 5 Category C 5940 Description 2/1/2019 $876.50 Any guidance would be appreciated. Thank you!
Date issues on a custom date
Hello, I have issues to associate dates correctly. I don't really know how to workaround this. Note: the second table is a view and i can modify it for my needs. I have dimension date table as follow: I have an other table with products and dates like this (i show you only dates here): I made a relation between them: I want to count customer by entity for a specific report period, so i made a segment on the Generic_Period. I created mesures for this segment: Customers = CALCULATE(DISTINCTCOUNT('Customers'[Customer]), filter(Customers, Customers[Generic_Period] = DATEADD(Customers[Generic_Period],0,MONTH))) Customers M-1 = CALCULATE(DISTINCTCOUNT('Customers'[Customer]), filter(Customers, Customers[Generic_Period] = DATEADD(Customers[Generic_Period],-1,MONTH))) The first probleme is Q-1 returns 0 values : The second problem, It doesn't work if the day number isn't the same as previous month. If i select 30/09/2019 it will not retrieve the 31/10/2019 even with end of month -1 like this : ENDOFMONTH(DATEADD(Subscription_Partner_by_month[Generic_Period],-1,MONTH)) Thanks a lot for your time.FILTER SELECTEDVALUE unable to conver text type into true/false
I am looking for values in a previous time range. But the problem is i can't use my filters segments ! Q-1 = CALCULATE(COUNT('Client'[Client Name]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-1,Quarter), FILTER('Client', SELECTEDVALUE('Client'[Entity]) )) 1) My entity are the country codes : UK, US etc ... how can i perform this ? 2) Whats the difference between ALLSELECTEDVALUES and SELECTEDVALUE ? Does selectedvalue will take in count all my segments ?
