Forum Discussion
count data report visuals
Im getting data from sharepoint list and prepared the visuals on count for each date and issue is we need to display count 0 when POcreatedwhen column po not created yeserday (since im manually creating this entry in sharepoint list that there was no data )
SO expected output
Date TotalPO pocreatedhwhen
27-03-2025 0 POcreatedNotYesterday
Issue is my below calculations is showing TotalPO for 27-03-2025 is 1 as per sharepoint list item but i need to set condition POcreatedWhen =POCreatedNot Yesterday as 0 in TotalPO column in visuals
Actual things in power BI
TotalPO measure formula
Report Visuals
sharepoint list data sample
Hi bzeeblitz ,
Thank you for reaching out to the Microsoft Fabric Forum Community.I've carefully reviewed your requirements and have reproduced your scenario using sample data. I'm happy to report that I've found a solution that provides the output you're looking for.
To achieve this, I used a combination of two measures within Power BI. Here's a breakdown:
- This measure handles the count for each individual row in your visual, displaying '0' when POCreatedWhen is "PO Not Created Yesterday" and counting the Purchase Orders when POCreatedWhen is "PO Created Yesterday".
TotalPO = VAR CurrentDate = SELECTEDVALUE(HistoricalPOdata[Date]) RETURN IF( MAX(HistoricalPOdata[POCreatedWhen]) = "PO Not Created Yesterday", 0, CALCULATE( COUNT(HistoricalPOdata[Title]), FILTER( ALL(HistoricalPOdata), HistoricalPOdata[POCreatedWhen] = "PO Created Yesterday" && HistoricalPOdata[Date] = CurrentDate ) ) )- This measure is specifically designed to calculate the total, ensuring it sums only the TotalPO values where POCreatedWhen is "PO Created Yesterday". This overrides the default total calculation.
TotalPOCreatedYesterday = CALCULATE ( SUMX(VALUES(HistoricalPOdata[Date]), [TotalPO]), FILTER ( ALL ( HistoricalPOdata ), HistoricalPOdata[POCreatedWhen] = "PO Created Yesterday" ) )To help you get started, I've attached a sample .pbix file that demonstrates this solution. You can download it and explore the measures and visual setup directly.
If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
9 Replies
Hey there!
It seems like you're trying to show the count of "PO Created When" for each date, but you want to adjust the condition to show a count of 0 when the POCreatedWhen column is marked as "PO Created Not Yesterday" for the date 27-03-2025.
In your current formula:
Total_PO = COUNT(HistoricalPOdata[Title])
This will simply count the number of rows in the HistoricalPOdata table for each date.
To adjust your visual and condition, you need to modify the measure to consider whether the POCreatedWhen column matches "PO Created Not Yesterday" and return 0 for that scenario. You can achieve this by using a conditional DAX expression:
Total_PO =
IF(
MAX(HistoricalPOdata[POCreatedWhen]) = "PO Not Created Yesterday",
0,
COUNT(HistoricalPOdata[Title])
)MAX(HistoricalPOdata[POCreatedWhen]) checks the value in the POCreatedWhen column for the current context (i.e., the current date in the visual).
If it matches "PO Not Created Yesterday", the measure will return 0.
Otherwise, it will count the number of Title entries (which corresponds to your original logic).
This should give you the desired result where the count is 0 when POCreatedWhen is "PO Not Created Yesterday" for the specific date 27-03-2025.
Hoep this helps!
😁😁
Yes I updated as you said but I'm getting total zero in below visuals
Date. Totalp pocreatedwhen
3/27/2025. 0. Ponotcreatedyesterday
2/26/2025. 29. Pocreatedyesterday
Total. 0
So it works partially but total is 29 but it's showing 0
Hey
the problem might be that the measure being used for the total is not calculating correctly across multiple rows.
You can use the ALLSELECTED() function to modify how the total is calculated. This will help ensure that the total calculation behaves correctly:
TotalPO =
IF(
MAX(HistoricalPOdata[POCreatedWhen]) = "PO Not Created Yesterday",
0,
CALCULATE(COUNT(HistoricalPOdata[Title]), ALLSELECTED(HistoricalPOdata))
)This will keep your row-level count logic the same but adjust the total calculation.
I hope this prints out 29!
Let me know if it works 😁😁
Hi bzeeblitz
Try this:
VAR _count = CALCULATE ( [count of po], KEEPFILTERS ( 'table'[POCreatedWhen] <> "PO Not Created Yesterday" ) ) RETURN IF ( SELECTEDVALUE ( 'table'[POCreatedWhen] ) = "PO Not Created Yesterday", 0, _count )This will exclude PO Not Created Yesterday from the total as well.
- Please clarify wats this CALCULATE (
[count of po] in your above formula as I don't use anything as count of po and pls clarify
Above formula contains error Return If is not recognized i altered to Return In but I'm facing errors multiple columns cannot be converted to a scalar value
Hi bzeeblitz ,
Thank you for reaching out to the Microsoft Fabric Forum Community.I've carefully reviewed your requirements and have reproduced your scenario using sample data. I'm happy to report that I've found a solution that provides the output you're looking for.
To achieve this, I used a combination of two measures within Power BI. Here's a breakdown:
- This measure handles the count for each individual row in your visual, displaying '0' when POCreatedWhen is "PO Not Created Yesterday" and counting the Purchase Orders when POCreatedWhen is "PO Created Yesterday".
TotalPO = VAR CurrentDate = SELECTEDVALUE(HistoricalPOdata[Date]) RETURN IF( MAX(HistoricalPOdata[POCreatedWhen]) = "PO Not Created Yesterday", 0, CALCULATE( COUNT(HistoricalPOdata[Title]), FILTER( ALL(HistoricalPOdata), HistoricalPOdata[POCreatedWhen] = "PO Created Yesterday" && HistoricalPOdata[Date] = CurrentDate ) ) )- This measure is specifically designed to calculate the total, ensuring it sums only the TotalPO values where POCreatedWhen is "PO Created Yesterday". This overrides the default total calculation.
TotalPOCreatedYesterday = CALCULATE ( SUMX(VALUES(HistoricalPOdata[Date]), [TotalPO]), FILTER ( ALL ( HistoricalPOdata ), HistoricalPOdata[POCreatedWhen] = "PO Created Yesterday" ) )To help you get started, I've attached a sample .pbix file that demonstrates this solution. You can download it and explore the measures and visual setup directly.
If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
