Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
The data as displayed in the visuals is:
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
Solved! Go to Solution.
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.
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.
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |