Calculating total in a card with filters

[Anonymous]

Hi everyone!

Looking for help in the following issue 🙂

In my report I have a matrix that shows different calculations -one of them is "Up Sale amount". 

(It is important to mention that this matrix is affected by filters that I added as well).

What I want is to add a card to the report with the "Up Sale amount" measure, but the problem is that I get another result as not all filters are working in the card. 

This is the measure in a matrix: 

And this is what I get in the card:

So I created the following meaure : 

upsale amount filtered for =

VAR __DS0FilterTable =

TREATAS({3}, 'Dim_Employees'[ID_Employee_Function])

VAR __DS0FilterTable2 =

FILTER(

KEEPFILTERS(VALUES('Dim_Employees'[ID_Full_EMP_Name])),

NOT(ISBLANK('Dim_Employees'[ID_Full_EMP_Name]))

)

VAR __DS0FilterTable3 =

FILTER(

KEEPFILTERS(VALUES('Dim_Customers'[ID_Customer_Name])),

NOT(ISBLANK('Dim_Customers'[ID_Customer_Name]))

)

VAR __ValueFilterDM3 =

FILTER(

KEEPFILTERS(

SUMMARIZECOLUMNS(

'Dim_Employees'[ID_Full_EMP_Name],

'Dim_Customers'[Customer_Name],

__DS0FilterTable,

__DS0FilterTable2,

__DS0FilterTable3,

"SumUpsale_Amount", CALCULATE(SUM('vwbi_order'[Upsale_Amount])),

"Total_Unique_Customers", IGNORE('Measures For Calls Reports'[Total Unique Customers]),

"SumAmount_Defacto", IGNORE(CALCULATE(SUM('vwbi_order'[Amount_Defacto])))

)

),

AND(

NOT(ISBLANK([Total_Unique_Customers])),

NOT(ISBLANK([SumAmount_Defacto]))

)

)

VAR __DS0Core =

SUMMARIZECOLUMNS(

__DS0FilterTable,

__DS0FilterTable2,

__DS0FilterTable3,

__ValueFilterDM3,

"@SumUpsale_Amount", CALCULATE([Total Upsale Amount])

)

RETURN

SUMX(

__DS0Core,

[@SumUpsale_Amount])

But the measure returns an error: 

Do you know how can I fix this? 

[v-janeyg-msft]

@Anonymous  Any updates?

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If problem still persists, pls let me know.

Best Regards,

Community Support Team _ Janey

[v-janeyg-msft]

Hi, @Anonymous 

The context of matrix is much more complicated than that of card, and your measure is also very complicated, leading to problems. I think the fastest way is to re-write a 'total' in card according to your logic instead of using the measure in the matrix. 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,

Community Support Team _ Janey

[HotChilli]

It looks like you've copied the DAX generated for the matrix visual (and maybe edited it as well).  I don't think this is a good way to continue.

There will be a simpler way to write a measure to get the value on a card.

I suppose you have to work out why there is a difference between the 437m and 443m. You say "not all filters are working in the card" - why is that?  and what does the total upsale measure look like?

[Anonymous]

Hi 🙂

The measure is very simple - I aggregated a column with sum function. 

But the issue that I added 2 filters on the matrix that led to different total amount in the measure, So I want to add those filters to the card in order to get same result. 

As I can't add the filters to the card itself I try to create a measure which will do it, but today I have problem with that as it doesnt work....

This is the measure: 

Total Upsale Amount =

SUM(vwbi_order[Upsale_Amount])

The filters that I want to add - 

vwbi_order'[Amount_Defacto] not blank

[Total Unique Customers] not blank

'Dim_Employees'[ID_Full_EMP_Name] not null

[Ashish_Mathur]

Hi,

Does this measure work?

=calculate([Total Upsale Amount],filter(values('Dim_Employees'[ID_Full_EMP_Name]),[Total Unique Customers]<>blank()))

[Anonymous]

I have spent the last 2 hours looking for this answer. Thanks so much!

[Ashish_Mathur]

You are welcome.

[Anonymous]

Hi Ashish Mathur!

yes it works!

But I have 3 filters - so I need to add another one from another table - 

vwbi_order'[Amount_Defacto] not blank,

and in the filter function it enables to use column from one table as I understand. 

Do you know how can I add this filter as well? 

[v-janeyg-msft]

Hi, @Anonymous 

Try:

measure =
CALCULATE (
    [Total Upsale Amount],
    'Dim_Employees'[ID_Full_EMP_Name] <> BLANK (),
    'vwbi_order'[Amount_Defacto] <> BLANK (),
    [Total Unique Customers] <> BLANK ()
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[Anonymous]

Hi!

Thank you for your response. 

This is te error I got when I used the query:

[v-janeyg-msft]

@Anonymous 

Try this:

measure =
CALCULATE (
    [Total Upsale Amount],
    FILTER (
        ALL ( 'Dim_Employees' ),
        'Dim_Employees'[ID_Full_EMP_Name] <> BLANK ()
    ),
    FILTER ( ALL ( 'vwbi_order' ), 'vwbi_order'[Amount_Defacto] <> BLANK () ),
    [Total Unique Customers] <> BLANK ()
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[Anonymous]

Hi !

UnfortunatelyI get same error 😞

[v-janeyg-msft]

@Anonymous 

You need to put all conditions in tables. I don’t know what your measure([Total Unique Customers]) is and which table is related to it. 

measure =
CALCULATE (
    [Total Upsale Amount],
    FILTER (
        ALL ( 'Dim_Employees' ),
        'Dim_Employees'[ID_Full_EMP_Name] <> BLANK ()
    ),
    FILTER ( ALL ( 'vwbi_order' ), 'vwbi_order'[Amount_Defacto] <> BLANK () ),
    FILTER ( ALL ( table ),[Total Unique Customers] <> BLANK ()
)

 I don’t know what your data and table look like, this code is just a rough idea.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,

Community Support Team _ Janey

[Anonymous]

'Dim_Employees'[ID_Full_EMP_Name] not blank*****