Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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
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
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.
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |