I’m looking for some help with Percent of total. I would like to add it to Table visual. I tried few expressions, percentofTotal function but with no luck because it shows 1(100%)in table or expression is wrong because of non(aggregated) fields.
So I have tags_key column in Group By dimension.
In Value I have resourcename(Count distinct) which shows how many unique resources are tagged with this tag.
I would like to add Tag Coverage in % which should represent % of all unique resources(resourcename)are tagged with this tag. Same case will be for cost assosiated with tag and tags_values but lets start from one - other will be similar
How I can achieve it properly?
What I tried:
I’ve created Calculate Fields:
Tags_DistinctcountResourcenameFixed to eliminate filtering be row and have distinct count static distinctCountOver(resourcename, [], PRE_FILTER)
Tag Coverage Percentage distinct_count(resourcename) / sumOver({Tags_DistinctcountResourcenameFixed})
But this one can’t be create because of error:
Field “Tags_DistinctcountResourcenameFixed” must be aggregated for table calculations.
I’ve tried also to create calculated field using percentoftotal function but percentOfTotal does not support distinct_count() aggregation yet as I found somewhere.
If you are intending to use table visual only, I suggest you to try changing visual type to pivot table and use Table calculation directly.
Alternately, you can try breaking down the calculation into numerator and denominator and use LAC functions to calculate the total value. This is needed if you plan to use this metric in other visuals (other than table).
THanks @prantika_sinha for your answer. Really appreciate it
However it only partially meet requirements. It is possible to apply some kind of filter/parameter/control etc. to ask these calculations to get only unique “resourcename” entries?
As You can see - for example 18 unique resources has tagA ,total distinct resourses is 155 so it should be 18/155 = ~12%
When comes to tag value it should calculate how many percentage of total is proper key value. For example - if there is 6 unique values and total unique key values are 60 it should show 10%
When comes to cost same as above it should calculate percentage of total cost. For example if there is 200 and total cost is 1000 should show 20%
@prantika_sinha I have these tags in json format in column Tags but I believe I can’t do such table based only on json format and separate it inside Quicksight… Or maybe I am wrong.
If it might be possible the problem with multiple values would dissapear…
Please create a sample analysis with mock data in Arena show casing your problem and post it along with clear notes on what values you are expecting to get for the calculation in couple of instances. This will help community members in helping you.
How to calculate Total billed cost but not for duplicated by tags values. So if resourcename has 4 tags and has 4 rows in same day it should take cost value only once.
So I would like to have
Billed cost calculated for unique resourceName. for example in bottom table for Resource_10 should take 15.32 only once for Jan2024 etc.
Total billed cost should by around 1300. Calculated only once per resourcename per day
2)Percentage of total(first table) should show
a) % of total unique resource so value/155 in this case
b) %of unique tags so value/70 in this case
c) % Total csot for distinct resources so value/ ~1300 in this cas
I was able to have value which represents cost divided by count of resource in same date so I am able to sum total cost with this new column but I faced issue with resources which contains others resource name like in the screen… (instead of divide it by 14(number of tags) , it divides by 28 because of other resource
I have tried to do the rest of the calculation, however the billing one is tricky.
For the rest of the calculations check out the ‘sample solution’ tab in Sample Solution 39553
However, in the second table if you see, 1 resource may have multiple tags. And since the required table view is at tag level, the cost will be duplicated. How do you want to calculate cost for this case?
Unfortunately @prantika_sinha your dashboard do not work for me ### Unexpected error
However I finally did it and it looks like it works as expected.
Cost is calculated by tag assosiated with cost.
%ofCost is calculated as tag cost coverage based on total cost for unique resources. I used FixedCost to have static Cost value independent from particular row.
