count data report visuals

[bzeeblitz]

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

[v-ssriganesh · Accepted solution]

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.

[danextian]

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.

[bzeeblitz]

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

[bzeeblitz]

• 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 

[freginier]

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!

😁😁

[bzeeblitz]

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

[bzeeblitz]

I used table visuals and so total is automatically showing as 0 but it should be 29

[freginier]

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 😁😁

[bzeeblitz]

It's printing 30 and it's counting as 1 not 0 as we put in our logic pocreatedwhen is 0 but it counts as 1 as per SharePoint list

So I see below outputs in visuals

Date.       Totalpo.    Pocreatedwhen

March1.      1  Ponotcreatedyesterday  fab2.        29. Pocreatedyesterday

Total.   30