Calculations are not matching (Results compared between PBi and Excel)

[Anonymous]

I came across a problem where the results in Power Bi is not bein calculated correctly (my assumption). I copied the data to excel and created a Pivot Table, using the same filters that I applied to Power Bi. The Excel result is correct.

It's the same data for both Pbi and Excel. 

IMAGE 1 and IMAGE 3: It's a comparisson between the results in PBi and Excel (you can see the filters applied in the Pivot Table).

IMAGE 2: Shows the DAX formula I used.

Z CREATED Opportunities FILTERED = calculate(countrows('view_opportunities'),
FILTER('view_opportunities', 'view_opportunities'[is_active_text] == "true"),
FILTER('view_opportunities', 'view_opportunities'[is_won_text] == "false"),
FILTER('view_opportunities', 'view_opportunities'[is_closed_text] == "false"),
Filter('User_Roles', 'User_Roles'[Active] == "yes"),
Filter('User_Roles', 'User_Roles'[role] == "Sales - Generalist"),
userelationship('monthtable'[Month(Full)],'view_opportunities'[MONTH_Opp_Created]
))

Any ideas and Suggestions?

[v-chenwuz-msft]

Hi @Anonymous 

Measure will calculate depend on the context, if you don’t add ALL() or ALLEXCEPT() to the filter’s table, it will calculate only the rows where Sale person = Adriano for the first rows in the matrix table visual.

And try measure like the following :

Z CREATED Opportunities FILTERED =
CALCULATE(
    COUNTROWS( 'view_opportunities' ),
    FILTER(
        ALL( 'view_opportunities' ),
        'view_opportunities'[is_active_text] == "true"
            && 'view_opportunities'[is_won_text] == "false"
            && 'view_opportunities'[is_closed_text] == "false"
    ),
    FILTER(
        ALL( 'User_Roles' ),
        'User_Roles'[Active] == "yes"
            && 'User_Roles'[role] == "Sales - Generalist"
    ),
    USERELATIONSHIP ( 'monthtable'[Month(Full)], 'view_opportunities'[MONTH_Opp_Created] )
)

Best Regards

Community Support Team _ chenwu zhu

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

[PaulDBrown]

Please share sample data (not an image) or PBIX file.

[Anonymous]

Nobody?