Forum Discussion

rachaelwalker's avatar
rachaelwalker
Resolver III
1 year ago

percentage using values from two different tables

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. 

 

 

 

"Need Help"

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

    • rachaelwalker's avatar
      rachaelwalker
      Resolver III
      1 year ago

      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!