Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I want to get a measure that sums up the total extension of the store, which should be 98 as per example below, that it sums if it has different values in column "extension".
And then if I filter either by entrance or warehouse I get the corresponding number as well:
store | extension | zone | |
45245 | 43 | entrance | |
45245 | 55 | warehouse | |
45245 | 55 | warehouse | |
45245 | 55 | warehouse | |
45245 | 43 | entrance | |
45245 | 55 | warehouse | |
45245 | 43 | entrance |
Obviously I have more that one store in my datababe and lots of different category values for each warehouse and entrance rows.
I am sure it is very simple but I don't seem to find an answer even if I researched for a while.
Thanks a lot
Solved! Go to Solution.
Hi @Arashi
Here's a measure that follows a similar pattern to others in this thread. It uses SUMMARIZE to build a table of unique Store and Extention values, then the SUMX aggregates the Extention values from there.
Max. de Extention =
SUMX(
SUMMARIZE('Table','Table'[Store],'Table'[Extention]),
'Table'[Extention]
)
Used in a matrix you get this
Hi @Arashi
Here is a sample file with the solution https://www.dropbox.com/t/lDoepZEuJV6GCl4m
The measure is
Extension Sum =
SUMX (
SUMMARIZE ('Table', 'Table'[Type of Store],'Table'[Store] ),
CALCULATE (
SUMX (
SUMMARIZE ( 'Table', 'Table'[Zone], "@Extention", MAX ( 'Table'[Extention] ) ),
[@Extention]
)
)
)
Hi, @Arashi
I can't get 1225 from the data above, would you be able to consider sharing more data and the output you expect?
It makes it easier to give you a solution.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello,
I have attached in the table below the dummy set data.
The expected result is as below:
For each store I want to get the sum of the maximum value of the extension column for each type of zone.
The measure provided by @daXtreme works perfectly in that sense.
However, I also want to get the aggregate of the type of store as the sum of each of the resultant value of stores based on previous measure.
Category | Zone | Store | Extention | Type of Store | Units |
Category5 | Warehouse | 1206 | 136,6 | E | 0 |
Category5 | Warehouse | 2052 | 102,06 | C | 217 |
Category5 | Warehouse | 2066 | 168,07 | E | 85 |
Category5 | Warehouse | 2067 | 117 | C | 174 |
Category5 | Warehouse | 8329 | 116 | C | 226 |
Category5 | Warehouse | 8334 | 0 | B | 0 |
Category5 | Warehouse | 8404 | 0 | D | 0 |
Category4 | Warehouse | 8750 | 118,92 | A | 639 |
Category4 | Warehouse | 13922 | 0 | B | 0 |
Category4 | Warehouse | 14009 | 133,67 | D | 160 |
Category4 | entrance | 1206 | 279,96 | E | 0 |
Category4 | entrance | 2052 | 351,11 | C | 313 |
Category4 | entrance | 2066 | 369,54 | E | 90 |
Category4 | entrance | 2067 | 322 | C | 386 |
Category4 | entrance | 8329 | 439 | C | 338 |
Category4 | entrance | 8334 | 313,48 | B | 503 |
Category4 | entrance | 8404 | 192,86 | D | 220 |
Category3 | entrance | 8750 | 355,63 | A | 911 |
Category3 | entrance | 13922 | 337,65 | B | 838 |
Category3 | entrance | 14009 | 281,71 | D | 225 |
Category3 | Warehouse | 1206 | 136,6 | E | 0 |
Category3 | Warehouse | 2052 | 102,06 | C | 196 |
Category3 | Warehouse | 2066 | 168,07 | E | 254 |
Category3 | Warehouse | 2067 | 117 | C | 136 |
Category3 | Warehouse | 8329 | 116 | C | 132 |
Category3 | Warehouse | 8334 | 0 | B | 7 |
Category2 | Warehouse | 8404 | 0 | D | 0 |
Category2 | Warehouse | 8750 | 118,92 | A | 313 |
Category2 | Warehouse | 13922 | 0 | B | 0 |
Category2 | Warehouse | 14009 | 133,67 | D | 52 |
Category2 | entrance | 1206 | 279,96 | E | 0 |
Category2 | entrance | 2052 | 351,11 | C | 489 |
Category2 | entrance | 2066 | 369,54 | E | 231 |
Category2 | entrance | 2067 | 322 | C | 545 |
Category2 | entrance | 8329 | 439 | C | 699 |
Category2 | entrance | 8334 | 313,48 | B | 1319 |
Category2 | entrance | 8404 | 192,86 | D | 346 |
Category1 | entrance | 8750 | 355,63 | A | 1717 |
Category1 | entrance | 13922 | 337,65 | B | 1860 |
Category1 | entrance | 14009 | 281,71 | D | 366 |
Let me know if now it is clear or if you need any other explanation.
Thanks a lot
Hi @Arashi
Here's a measure that follows a similar pattern to others in this thread. It uses SUMMARIZE to build a table of unique Store and Extention values, then the SUMX aggregates the Extention values from there.
Max. de Extention =
SUMX(
SUMMARIZE('Table','Table'[Store],'Table'[Extention]),
'Table'[Extention]
)
Used in a matrix you get this
It works perfectly.
I have a lot to learn yet!
Thank you very much!
Hi @Arashi
you may try
Extension Sum =
SUMX (
SUMMARIZE ( Table, Table[Zone], "@Extention", MAX ( Table[Extention] ) ),
[@Extention]
)
Thanks for your answer. However I get stucked at "@Extention". It gives me an error when I type "
Maybe I am not understanding propperly and it is a different value I should input.
Thanks
I did not try to be honest. But it could be true as SUMMARIZE is thr not the best iption to add columns. You can try
Extension Sum =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( Table, Table[Zone] ),
"@Extention", MAX ( Table[Extention] )
),
[@Extention]
)
Something easier (and might be faster):
Extension Sum =
sumx (
distinct( Table[Zone] ),
calculate ( max ( Table[Extention] ) )
)
Hello,
This solution works well. However when I use this measure in a matrix with aggrupated stores, for the type of store also provides a wrong calculation:
Expected result for C stores subtotal should be the sum of the three stores total extention = 1225.
Can you help with this?
Thanks so much!
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |