topic Re: percentage using values from two different tables in DAX Commands and Tips

percentage using values from two different tables

rachaelwalker — Thu, 17 Oct 2024 15:52:05 GMT

I have two tables Projects and Warranty Tickets. I need to calculate the percentage of projects that have had a warranty ticket opened against it. In some cases, there are multiple tickets but I dont want that to impact the the percentage. For the example below, I would expect to see 15 projects have had tickets out of the 101 total = 14.9%. I am also looking for the same percentage but only calcuate the last 90 days based on date entered while ignoring any other filter visuals that may be applied.

Re: percentage using values from two different tables

lbendlin — Thu, 17 Oct 2024 21:05:47 GMT

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.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: percentage using values from two different tables

rachaelwalker — Fri, 18 Oct 2024 17:43:51 GMT

lbendlin thank you for the tips. here is a pbix file with sample data: https://drive.google.com/file/d/1_ZphAFTa0eAJgpajmEk4D2jDBLPB7HSm/view?usp=sharing

Re: percentage using values from two different tables

lbendlin — Fri, 18 Oct 2024 22:42:48 GMT

What does it mean when tickets are entered before the warranty start date?

Why do some projects not have a warranty start date?

I am also looking for the same percentage but only calcuate the last 90 days based on date entered

100% by definition. Unless you mean "last 90 days based on warranty start date"?

Re: percentage using values from two different tables

rachaelwalker — Mon, 21 Oct 2024 13:21:18 GMT

The project may consist of several phases. The warranty date is defined at the final phase, marking the date when official completion of the project. Alternatively, it may arise if the project manager deviates from processes. Very frustrating I know. I told them that I will provide the data/metrics, along with an explanation of how it's calculated, so they can assess whether a process change is necessary.

If there is no warranty date, the project is not yet under warranty, and no tickets should be submitted against it. I included the entire dataset in case I need to apply a filter to the formula.

Thank you for asking these questions to better understand the data. I appreciate your help and expertise!

Re: percentage using values from two different tables

rachaelwalker — Mon, 21 Oct 2024 13:21:47 GMT

lbendlin forgot to tag

Re: percentage using values from two different tables

Anonymous — Tue, 22 Oct 2024 06:31:41 GMT

Hi lbendlin ,thanks for the quick reply, I'll add more.

Hi rachaelwalker ,

I am also looking for the same percentage but only calcuate the last 90 days based on date entered while ignoring any other filter visuals that may be applied.

Your requirement is to calculate data with a warranty effective date within 90 days right? Suppose today is 10/22/2024,90 days before the date is 7/24/2024.The filter condition is 'Project Warranty Start' >= 7/24/2024 , 'Date Entered' >= 7/24/2024.If I understand it incorrectly Please correct me.

Use the following DAX expression to create a measure

Measure = VAR _a = CALCULATE(DISTINCTCOUNT('Table'[Project Number]),'Table'[Date Entered] >= TODAY() - 90 && 'Table'[StartDate] >= TODAY() - 90 ) VAR _b = COUNTROWS(FILTER('Projects',[Project Warranty Start] >= TODAY() - 90)) RETURN DIVIDE(_a,_b)

Best Regards,
Wenbin Zhou

Re: percentage using values from two different tables

rachaelwalker — Tue, 22 Oct 2024 13:18:31 GMT

The warranty effective dates are between (Project Warranty Start Date) and (Project Warranty Date + 90 Days) so not 90 Days from today but from the Project Warranty Start. If a ticket is entered between between those dates, it goes against the project. There could be multiple tickets entered against a project but I dont want that to impact the %. Hopefully I am making sense

Anonymous I will plug these in and see how it looks against my dataset. Thank you!