Countrows giving improper aggregation

[toronto_powerbi]

I am trying to count for which day sales cross a threshold level but when I remove "day" column it shows 60 and , instead it should be 58 and 2 (total should be 60 as that is for 2 companies multiplied by 30 days).

Any help would be appreciated

[toronto_powerbi]

Hey there, thank you for the prompt response. It seems like sumx does not work with count/countrows. I have pasted my solution both using sumx and the orginial dax. It would be helpful if you could point me in the right direction.

Intent is to get number of days where orders exceed a threshold in a month. Therefore, my original response was giving 60 which should have been 58. That dax should work when I just have months in my rows and the calculated measure should show in this month how many days was orders were within limit.

Deliveries at Normal Level all days =

 var normal_delvr = CALCULATE(COUNT(fleet_deliveries[Orders]),FILTER((fleet_deliveries),

                                        fleet_deliveries[Type] = "Delivery"

                                        && SUM(fleet_deliveries[Orders])< SUM(fleet_deliveries[Normal])

                                        &&  fleet_deliveries[Status_Flag] <> "Cancelled Contract"))

var normal_delvr_sumx = SUMX(

                            SUMMARIZE(

                                    CALCULATETABLE(fleet_deliveries, fleet_deliveries[Type] = "Delivery"

                                        && SUM(fleet_deliveries[Orders])< SUM(fleet_deliveries[Normal])

                                        &&  fleet_deliveries[Status_Flag] <> "Cancelled Contract") ,

                                         "counts",

                                          COUNT(fleet_deliveries[Orders])

                            ), "counts")

RETURN  normal_delvr

[v-tangjie-msft]

Hi @toronto_powerbi ,

I looked at your dax formula, for your table structure and I am not very clear.
For your question, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[toronto_powerbi]

When I select two companies, company A & company B my Dax does not throw correct output. For example, The normal column is a threshold column and if my orders are less than normal then it should be 2 (since two companies are selected). Therefore, the sum total of column should be 58 instead of 60 (1 april month X 2 companies = total 60 days). 

My dax works when only 1 company is selected and it correctly shows if orders are less than threshold then 1. 

My desired output is something like this: In the following months how many days were deliveries at normal level which is filterable btw companies as shown in the dummy power Bi file.

Here is my power Bi link -> Power BI Link   

[v-tangjie-msft · Accepted solution]

Hi @toronto_powerbi ,

According to your description, here are my steps you can follow as a solution.

(1)You can create a masure.

Measure = SUMX( 'filter_date' , [Deliveries at Normal Level all days attempt_v1])

(2)Then the result is as follows.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[toronto_powerbi]

This is exactly what I was looking for. Thank you! 🙂

[v-tangjie-msft]

Hi @toronto_powerbi ,

According to your description, you are showing the wrong value in the Total of the visual object. 
First of all, you should be using a metric value, which is not simply summed when calculating Total, but calculates your corresponding metric formula based on the current filtering context, so the resulting value does not match your expected value. 
You can use the following methods to avoid this situation. 
(1) Using iterative functions such as SUMX() to calculate the value. 
(2) Use calculated column to calculate the value you want and then put it in the visual object. 
(3) Use the HASONEVALUE() function to replace the Total value you want. 

For more information you can refer to the following links: 

Calculated Column and Measure in Power BI (powerbiconsulting.com) 

Power BI DAX SUM and SUMX function - How to use - EnjoySharePoint 

HASONEVALUE function (DAX) - DAX | Microsoft Learn 

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.