Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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.

View solution in original post

9 REPLIES 9
prateekraina
Memorable Member
Memorable Member

Hi @Anonymous,

 

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

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

 

Prateek Raina

Anonymous
Not applicable

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

Hi @Anonymous,

 

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

Anonymous
Not applicable

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

Hi @Anonymous,

 

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

 

Prateek Raina

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.

Anonymous
Not applicable

Thank you Very mutch @prateekraina

 

That measure worked like a charm!

Have a great day, i sure will now 😉

- Robin

Hi @Anonymous,

 

Glad to know. Kindly hit the thumbs up as well 🙂

 

Prateek Raina

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.