Multiple KPI with YTD and Monthly View

I am am replicating a Multi KPI Page in Power BI to Quicksight

Users have a choice for KPI and Period Calculation as below


image
To view the Chart below

Also note, the chart respects the %,$ and # formatting.
Calling all quicksight wizards.
Thanks in advance.

I would make two parameters. One for the KPI. One for Period Calculation.

Then I would make two calculated fields. One that is an ifesle statement that changes the calculation based on your KPI.

calculated_kpi_example = ifelse(${KPI}=‘New Logo $’, sum(sales),${KPI}=‘New Logo #’, distinct_count({sale_id}),etc…)

After you have that I would then make a different calculated field that toggles weather it’s monthly or YTD.

ifelse(${MOrYTD}=‘Monthly’,calculated_kpi_example ,runningSum(calculated_kpi_example , [truncDate(“MM”,{Date}) ASC], [truncDate(“YYYY”,{Date})]))

Let me know if that helps!

Hi,

As suggested by Max, you can implement it at your end with a few additional tweaks that are required since a calculated field can contain 1 type of formatting , for eg : number or percent. Since you charts respect formatting , here a solution i had provided when metrics have different formatting : Conditional Formatting & Assigning Metric Format based on Parameters

Kind Regards,
Koushik

Hi @Max , i did something similar as you suggested.
However i had to create calculated fields for each KPI as per the defination then reference these calculated fields within the measure you defined above (calculated_kp_example) since i was getting error related to multiple nesting for my next calculated measure for Monthly/YTD

Hi @Koushik_Muthanna,
Good workaround but conditional formatting only works for tables in quicksight.
Tables are fine for Granular data at account or transaction level. I would’nt use table to tell a story which is a purpose of visualisation tool.

Admin please don’t mark this as a solution.

Hi,
Could you post a screenshot of what you were able to test so far ?

You can ignore the conditional formatting in my other post, I addressed it, as it was part of the question.

Below is an example of the same working for a table and a bar chart ( I understand you are looking at a bar chart from your initial screenshot ) .

QS_Dynamic_Metrics


Thanks for the screenshot , looks good so far :slight_smile:

Are there still any open questions on this topic ?

Yes, I dont have the solution for formatting yet.
Sorry i cant see details in your video if this solution lies in there ?

Please look at this post where formatting is numeric and percent as part of the metrics (visible in the screenshots ) ( ignore conditional formatting) : Conditional Formatting & Assigning Metric Format based on Parameters)

@Koushik_Muthanna based on your link above do i need to create a dozen of visuals if i have that many kpi’s to show or work around with 3 visuals with $,% and # type formatting ?

As of now that is the only way to change the type of formatting