cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Rovand Regular Visitor
Regular Visitor

Need help Card visual

Hi

 

Im trying to make a Card visual of the amount purchased with the value under 400 NOK (Norwegian kroner)

My problem is that its base on order numbers that can be spread on multiple rows. So i need the measure to add up the total per order number and calculate and count if the total is under 400 NOK.

 

 

2018-08-01_12-28-54.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
prateekraina New Contributor
New Contributor

Re: Need help Card visual

Anyway, I have created the measure which should work as i have tested it well with dummy data.
Here it goes:

AmountUnder400 = 
VAR CalcTable = SUMMARIZECOLUMNS(
    Table1[Order Number]
    ,"Purchase Amount"
    ,SUM(Table1[Purchase Amount])
    )
RETURN
CALCULATE(DISTINCTCOUNT(Table1[Order Number]),
    FILTER(CalcTable,
        [Purchase Amount] < 400
    )
)

Just use this measure in the card. Thats it. No calculated table required.
Let me know if it works.

 

Prateek Raina

Edit 1: Also, here is the pbix file where I created this for your reference. It has both approaches which I have showed you.

9 REPLIES 9
prateekraina New Contributor
New Contributor

Re: Need help Card visual

Hi @Rovand,

 

Here is the measure:

AmountUnder400 = 
CALCULATE(
    COUNT(Table1[Order Number]),
    FILTER(
        Table1,
        Table1[Purchase Amount] < 400
    )
)


If your order numbers are going to repeat then DISTINCTCOUNT should be used instead of COUNT.

AmountUnder400 = 
CALCULATE(
    DISTINCTCOUNT(Table1[Order Number]),
    FILTER(
        Table1,
        Table1[Purchase Amount] < 400
    )
)



Prateek Raina

prateekraina New Contributor
New Contributor

Re: Need help Card visual

Sorry, the above solution wil not work if we have same order numbers repeating. Let me get back to you.

 

Prateek Raina

prateekraina New Contributor
New Contributor

Re: Need help Card visual

Hi @Rovand,

 

I have a quick solution for you now so that you can go ahead in your report development.
However, I will get back with more efficient solution which will be done by a single measure.

 

Here it goes:

1. Create a calculated table with below code.

CalcTable = SUMMARIZECOLUMNS(
    Table1[Order Number]
    ,"Purchase Amount"
    ,SUM(Table1[Purchase Amount])
    )

2. Create a measure with below code and use that in card.

AmountUnder400 = 
CALCULATE(
    COUNT('CalcTable'[Order Number]),
    FILTER(
        'CalcTable',
        'CalcTable'[Purchase Amount] < 400
    )
)

Replace tablename as per your schema and you are good to go.

 

Prateek Raina

Rovand Regular Visitor
Regular Visitor

Re: Need help Card visual

Hi @prateekraina

 

The last solution with the calc table dose not seem to work right, when i implement it with my original repport with over 40k rows, it dose not come out with the right result.

Currently it shows 7120 order under 400 when it should be 1403

Rovand Regular Visitor
Regular Visitor

Re: Need help Card visual

I can not for the life of me figure out why the calculated table dose not work, by all accounts it should

prateekraina New Contributor
New Contributor

Re: Need help Card visual

Hi @Rovand,

 

Thats crazy. It should have worked.
Can you by any chance share with me privately the actual file?

 

Prateek Raina

prateekraina New Contributor
New Contributor

Re: Need help Card visual

Anyway, I have created the measure which should work as i have tested it well with dummy data.
Here it goes:

AmountUnder400 = 
VAR CalcTable = SUMMARIZECOLUMNS(
    Table1[Order Number]
    ,"Purchase Amount"
    ,SUM(Table1[Purchase Amount])
    )
RETURN
CALCULATE(DISTINCTCOUNT(Table1[Order Number]),
    FILTER(CalcTable,
        [Purchase Amount] < 400
    )
)

Just use this measure in the card. Thats it. No calculated table required.
Let me know if it works.

 

Prateek Raina

Edit 1: Also, here is the pbix file where I created this for your reference. It has both approaches which I have showed you.

Rovand Regular Visitor
Regular Visitor

Re: Need help Card visual

Thank you Very mutch @prateekraina

 

That measure worked like a charm!

Have a great day, i sure will now Smiley Wink

- Robin

prateekraina New Contributor
New Contributor

Re: Need help Card visual

Hi @Rovand,

 

Glad to know. Kindly hit the thumbs up as well Smiley Happy

 

Prateek Raina