Forum Discussion
Helper IDynamically Display Columns With Totals Greater than X
Hi All,
I have a dataset in which each column is a service and each row is the monthly spend on that service.
There are multiple columns.
I would like to only display columns who's summed value across multiple rows exceeds a specified amount.
Im not sure how to go about this. I thought about a custom column with a true/false statement but would need one for each column which isnt feasable.
Edit: Below is a sample of my dataset. What i need to do is create a stacked bar chart for each "Service", for 3 months, where the summed column value is greater than $100.
| Service | Date | Backup($) | CloudFront($) | CloudTrail($) | CloudWatch Events($) | CloudWatch($) | Config($) | DynamoDB($) |
| Webstacks | ######## | 3.807453 | 0.036048 | 0 | 1.23E-05 | 31.74363 | 44.661 | 11.85437 |
| Webstacks | ######## | 2.603889 | 0.034417 | 0 | 8.22E-06 | 29.49403 | 50.823 | 11.50157 |
| Webstacks | ######## | 1.869805 | 0.025869 | 0 | 9.25E-06 | 30.19799 | 43.255 | 10.58426 |
| Webstacks | ######## | 54.0759 | 0.003578 | 0 | 6.17E-06 | 34.60747 | 44.946 | 11.85438 |
| Webstacks | ######## | 30.05327 | 0.009263 | 0 | 4.11E-06 | 32.6348 | 50.484 | 11.85437 |
| Webstacks | ######## | 23.14119 | 0.005703 | 0 | 7.2E-06 | 33.19429 | 40.972 | 10.58426 |
| Webstacks | ######## | 25.72632 | 13.80705 | 0 | 144.2591 | 20.979 | 11.51922 | |
| Webstacks | ######## | 27.07262 | 13.52358 | 0 | 140.8452 | 37.339 | 11.50157 | |
| Webstacks | ######## | 28.69985 | 9.080629 | 0 | 125.1161 | 21.766 | 10.1609 | |
| Legacy | ######## | 473.4893 | 2.618748 | 18 | 186.6134 | 55.705 | 8.891563 | |
| Legacy | ######## | 473.8729 | 3.475225 | 18 | 1.03E-06 | 191.1176 | 47.452 | 8.891565 |
| Legacy | ######## | 456.3025 | 3.35474 | 16 | 184.6953 | 35.739 | 7.938909 | |
| KLG | ######## | 3.973513 | 0.048337 | 196 | 3.46E-05 | 27.1338 | 431.07 | 11.51522 |
| KLG | ######## | 3.383487 | 0.068108 | 177 | 0.000207 | 28.7617 | 316.024 | 11.87038 |
| KLG | ######## | 4.543583 | 0.082573 | 205 | 0.000158 | 36.45886 | 244.503 | 26.54885 |
| KLG | ######## | 2.665644 | 0.002755 | 0 | 17.47552 | 87.859 | 11.85571 | |
| KLG | ######## | 2.624613 | 0.000723 | 0 | 8.24E-06 | 18.56673 | 86.799 | 11.856 |
| KLG | ######## | 2.520708 | 0.001428 | 0 | 1.85E-05 | 21.8817 | 84.005 | 30.76302 |
| KLG | ######## | 3.314312 | 0.243455 | 0 | 1.03E-06 | 50.76672 | 122.395 | 11.50283 |
| KLG | ######## | 4.060302 | 0.167486 | 0 | 6.18E-06 | 55.46416 | 117.683 | 11.8558 |
| KLG | ######## | 3.914725 | 0.001111 | 0 | 1.23E-05 | 84.95957 | 98.257 | 30.19538 |
5 Replies
- amitchandak
Super User
Rince91 , If a column is going to be measure you can make it blank like
if (sumx(allselected(Table), Table[Columm]) >100, sum(Table[Column]), blank())
If need you can create a calculation group on these measures to get better control
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0
Rince91Thanks for this, i can see how this would be really useful and i've subscribed!
However, i dont think it will do what im looking for. I have posted an edit with a sample data set and further explanation of what i am trying to do.
- v-yalanwu-msft
Community Support
Hi, Rince91 ;
Sorry I am still a little unclear about your question, first of all there are two columns in the dataset where the numbers are not very clear.
Secondly, you are asking for the sum of Backup($) + CloudFront($) + CloudTrail($) + CloudWatch Events($) + CloudWatch($) + Config($) + DynamoDB($) for each service and >100 right?
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- Rince91
Hey, Thanks for replying.
Unfortunately not quite right. Hopefully the below explanation clarifies things.
This is the graph i need to present:
However, i only want to show segments of the bar that are over a certain value.
Each item of the legend is a column in the data set.
I sum the value for each month for each column.
If that sum value is greater than $150, plot the sum value on the stacked graph against the relevant month.
- v-yalanwu-msft
Hi, Rince91 ;
Is your problem solved? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
