I have different columns representing various product suppliers at different levels. I want to create a table that calculates the total number of suppliers, regardless of their supply level, and display it on the QuickSight dashboard (basically, I have Table 1 and want to achieve Table 2).
Hello @MajidFa, in order to get the output for the 2nd table, you really need your provider columns to be joined into a single column. That would make this functionality really simple to implement.
It honestly might be too complicated of a data alteration to make the 2nd table work while checking values across 6 columns. There may be some alternate ways to display the data though. While trying to figure out a possible solution, I was able to put together a calculated field that could return the number of benz orders per week. It would look something like this:
Benz Total per Week = sumOver(ifelse({Provider 1.1} = 'benz' OR isNull({Provider 1.1}) AND {Provider 1.2} = 'benz' OR isNull({Provider 1.2}) AND {Provider 1.3} = 'benz' OR isNull({Provider 1.3}) AND {Provider 2.1} = 'benz' OR isNull({Provider 2.1}) AND {Provider 2.2} = 'benz' OR isNull({Provider 2.2}) AND {Provider 2.3} = 'benz' OR isNull({Provider 2.3}), {orders}, NULL), [{Week}], PRE_AGG)
But to get this calculation, along with the other provider types, into a single calculated field would be a bit of a daunting task.
If you can update the columns to show a single provider, I know we can work out an easier solution. Thank you!
The calculation model works such that if Benz appears in Provider 1.1 and 1.2, it should be counted twice, and if it appears four times, it should be counted four times (it’s important to note how many times a provider appears in a single row). Therefore, the method you mentioned doesn’t work because it only counts the provider once per row, whereas it should be included in the final total as many times as it appears.
as this involves some data transformation, I think it will be difficult and time consuming to handle in the visualization level. What is the data source? is it RDBMS or S3?
If it supports SQL, you can use custom SQL in QuickSight and write it like below to transform the data into 2 columns, then it should be straight forward to create a table visual to visualize it
select date, provider1.1
union all
select date, provider1.2
union all
select date, provider1.3
union all
select date, provider2.1
union all
select date, provider2.2
union all
select date, provider2.3
Ref: Using SQL to customize data - Amazon QuickSight
Hi @MajidFa, did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!
