Project Name, Category Name, and Portfolio Name are from a table called 'Projects"
Actual, Committed are from a table called 'Expenses'
Projects is a one to many relationship with Expenses
In the above scenario, the project "cancer test project 5" has no related records in the Expenses table, so it is showing as blank, is there a way to show 0 instead
I have tried doing the following in the Projects table, but it gave unexpected results: Actual = IF(ISBLANK(SUMX(RELATEDTABLE('Expenses'), [Actual])) = FALSE(), SUMX(RELATEDTABLE('Expenses'), [Actual]), 0)
Please note for data that has no value in the row it displays as 0.00 as shown in the screenshot but since the header part is derived it does not have data in database which I would like to display as 0.00 instead of Blank.
@Aks-1 a little tough to determine without seeing the table defitions, however, I would start by removing the VALUES() clause at the end of the CALCULATE function. The new dax measure would read as below:
It may also be that you need to use the "+ 0" after the closing paranthesis of the CALCULATE function.
By utilizing the VALUES function, you are effectively filtering the CALCULATE function to apply to only the unique "LY" values in the POC table. This may be causing the undesired result of blank values, for those LY values that are not in POC table.
I also have a similar issue that I just can't get my head around..and the "+0" method hasn't solved it.
I have a complex model, but for this purposes have stripped it backed to simple basics, and still can't resolve it, even though I have previously I think.
In simple terms we have a customer base which is not active in every month on every product.
Therefore when comparing one month of Sales with another, you get blank entries in the database as part of the dataload relating to Customers, such as follows (removing the product variable to keep things even simplier);
1 table (DATA) with 3 Members as follows Customer, Month and Sales
Cust1, Month1, 100
Cust1, Month2 ,200
Cust1, Month3, 50
Cust2, Month1, 75
Cust2, Month3, 40
Cust3, Month2, 78
Cust3, Month3, 80
So Cust1 is active is all Months, Cust2 active in all months but Month2, and Cust3 active is all months but Month1.
My issue is that I'm trying to generate a simple Sales Variance (current month - previous month) report across all months for all Customers. My totals balance, but the analysis by Customer is missing rows where when the Customer is not active in the "Current Month" - example table below for Month 2 as Current Month
Is there any idea how to reflect "0" when there is a list of countries in the table, but one country has no results at all? The goal is to show that all countries have smth even if it just zero, so the bad result would push it to add some products too.
I cannot even see the country in the table I get from database via Power bi, but i know it is there 🙂