Forum Discussion

ZR2023's avatar
ZR2023
Frequent Visitor
3 years ago

Only show transactions with no value

Hi - 

My source is a list of transactions with amounts and each transaction is listed as an expense or revenue and has a project identifier like the table below. In Power BI, I would like to see which projects have no revenue transactions recorded. What is the best way to do this? When I use a slicer and filter for sales = zero it doesn't seem to work as it shows me all projects anyways and just shows revenue as zero even though I know there are revenue transactions for that project. Thank you!!!

Project ABCRevenue$100
Project ABCExpense$50
Project XYZRevenue$50
Project DEFExpense$250
Need Help

3 Replies

  • digicontrolling's avatar
    digicontrolling
    Frequent Visitor
    3 years ago

    In Power BI, you can identify projects that have no revenue transactions by following these steps:

    1. Create a New Table for Revenue Transactions: Filter your original table to include only revenue transactions. You can do this by creating a new table using the query editor or a DAX formula like:

       
      RevenueTable = FILTER(AllTransactions, AllTransactions[Type] = "Revenue")
       

    2. Create a List of Projects with Revenue: From the RevenueTable, create a list of unique projects that have revenue transactions.

    3. Compare with All Projects: Compare this list with the list of all unique projects in your original table. Any project that is in the original list but not in the revenue list has no revenue transactions.

    4. Visualize the Result: You can visualize this result using a table or other visual in Power BI, showing only the projects with no revenue transactions.

    Here's a step-by-step breakdown:

    1. Create a Revenue Table: Go to the Modeling tab and create a new table with the DAX formula mentioned above.

    2. Create a List of All Projects: You can use a DAX formula like:

       
      AllProjects = VALUES(AllTransactions[Project])
       

    3. Create a List of Projects with Revenue: Similar to step 2, but using the RevenueTable:

       
      ProjectsWithRevenue = VALUES(RevenueTable[Project])
       

    4. Find Projects without Revenue: Use the EXCEPT function to find projects in AllProjects but not in ProjectsWithRevenue:

       
      ProjectsWithoutRevenue = EXCEPT(AllProjects, ProjectsWithRevenue)
       

    5. Create a Visual: Use the ProjectsWithoutRevenue table to create a visual that shows the projects without revenue.

    This approach ensures that you are only looking at projects that have no revenue transactions recorded, rather than projects that have zero revenue. Feel free to reach out if you have any further questions!

    • ZR2023's avatar
      ZR2023
      Frequent Visitor
      3 years ago

      Hi, thanks for the response. I would like to clarify some of your steps.

      1. I created a Revenue Table.

       

       

      2. I get this error:

       

      Wondering if firstnonblank formula may work better?

      By the way, did you mean to say from the original table: 

      1. Create a List of Projects with Revenue: From the RevenueTable, create a list of unique projects that have revenue transactions.

      And when you say "create a list" do you mean create using 'New Measure'?

       

      3. I get the same error as step 2.

       

      4. The Except formula is using tables, but I have not created tables as the output of #2 and #3 above.