Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shlomo
Frequent Visitor

Blanks in data not appearing in Total

I have the following formula which is used in the two visuals below:

 

Transactions - Target = 
CALCULATE (
    COUNTROWS (
        FILTER (
            CALCULATETABLE (
                MA_Main,
                MA_Main[Target Country] = "Canada",
                MA_main[Status] = "Completed"
            ),
            AND (
                [Total Value - Target] >= MIN ( Transaction_Size[Min] ),
                [Total Value - Target] <= MAX ( Transaction_Size[Max] )
            )
        )
    )
)

The table Transaction_Size is: 

Transaction_Size table

 

The data as displayed in the visuals is:

Bar Chart and Table showing transactions by size

 

The Bar Chart and the Table both show correct numbers for the categories, but the Total on the Table excludes the "Unknown" category. Transactions with missing (blank) amounts are shown in the Unknown category.

 

How do I get the Total to show the correct amount? As an alternation to the aforementioned formula and Transaction_Size table, I tried creating a formula using SWITCH, but even though there were no DAX errors, there was an error trying to visualize the data.

 

Any help is appreciated.

 

Ken

1 ACCEPTED SOLUTION
Shlomo
Frequent Visitor

Instead of trying to create a measure, I decided to create a calculated column. I believe the blanks in the column of the original formula prevented me from coming up with the correct formula.

View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

Aadd a calculated column with value 1 and instead of COUNTROWS use SUM on this new calculated column.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k I am not sure if I understand what you are suggesting. Do you mean to add a new calculated column and make every value 1 and then use SUM on this column? I am not just looking for a Total. I want the numbers in the table to add up to the correct Total. I need this because, otherwise, when I cross filter the column chart (left chart above) to another table (with category, amount and count columns), the count column ignores the transactions with a blank amount.

Shlomo
Frequent Visitor

Instead of trying to create a measure, I decided to create a calculated column. I believe the blanks in the column of the original formula prevented me from coming up with the correct formula.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.