Count percentage with distinct

[anno27]

I am having problem to count number of percentage with remove duplicate with distinct(to remove possibility of duplicate rows).

this is for table USA01, there is another tables with similar column.

I tried this formula :
distinctCountOver({Utilization(%)}, [{ROWS}], PRE_AGG) but error

FYI : for the field “Utilization(%)” I use this formula to get percentage :
(sum({Total Load})/avg({Capacity}))*1

final result, I would like to show number of percentage between 90%-100% and above 100%, expect to show graph like this :

[andres007]

Hi,

You can create 2 calculated fields with the % of each case

100+
countIf(site,{Utilization(%)}>=100)
90-100
countIf(site,{Utilization(%)}>90 AND {Utilization(%)}<100)

And use those for the graph:

Hope this helps!

[anno27]

I got error, maybe I use formula on field {Utilization(%)}
like this :

[anno27]

field percentage_value is Utilization(%)

[anno27]

should I change formula on field Utilization(%) / percentage_value ?
if yes, could you advice another formula to get same value

[andres007]

Not sure why you are using sum and avg for the % , can you try juat the division of the 2 values ×100?.

[anno27]

this is the raw data.
I need to use SUM and AVG are because of need to get total value of each Rows from column Load so I use SUM, and for capacity actually only 1 value for each Rows.

[anno27]

just try your advice : the division of the 2 values x 100, it seems the result is same with my formula :
fyi : I use Pivot table

and then I create new field countif(>100) and countif(90-100)

but the value is 0 (please see above screenshot).
so I try to change formula less than 100 (countIf(site,{Utilization(%)}<100), so become like this :

it seems the formula counting number on the raw data :

[andres007]

Looks like it is related to the fact you are using a pivot, can you try in a regular table first to see if the formula works as expected in a row by row basis?

I will need to try with pivot to see how to make this work.

[anno27]

yes, it works if use regular table

but I need to show percentage in total per Rows like in the pivot table

[anno27]

column countif(>100) I change using above than 3 (>=3)

[anno27]

I think I can marking above 100% and 90%-100% as value 1 with this formula =
Testing-1 =
ifelse((sum({ppd_value})/avg({cold_aisle_cooling_limit})*100)>100, 1, 0)

Testing-2 =
ifelse((sum({ppd_value})/avg({cold_aisle_cooling_limit})*100)>90 AND (sum({ppd_value})/avg({cold_aisle_cooling_limit})*100)<100 , 1, 0)

but I having problem to show it on Bar Chart, because there is duplicate C01-C16 in another room.
so the level like this =
Site => Room => C01…C16
example :

• USA1 => Room 2-2 => C01… until C16

Could you advice how to show in bar chart if there is duplicate C01-C16 in the other room

[andres007]

Hi,

What is shown in the bar chart, is what you see in a table, for representing something similar to what you see in the pivot table in a graph the only thing I can think of it to have the data agregated before it reaches QuickSight.

Is it possible to have the data aggregated by site/room in the source?

[anno27]

The value in the column of Total Cooling Load and Capacity are different dataset.

by the way, if possible to aggregated by site/room in the source, which formula should I use

[andres007]

The capacity is the same for every meassure so you should aggegate in the dataset of the lest, inside the Custom SQL something like a select site, rows, room, sum(load) group by site, rows, room