How to display the sum value in the KPI card based on the Select All in a slicer

[EthanChenJichao]

Hello everyone,

I added a slicer and a KPI card in the Power BI. When I choose Select all in the slicer, the value displayed in the KPI card is the value of the last item in the slicer, but not the sum value of all the slicer items in the Slicer. 

The attached is the pbix file. And the Slicer and KPI card mentioned are on the first report.

When I choose "Select all" in the "TTM Periods" slicer, the value dislayed in the KPI card above only displays the value of the "TTM-4", but not the total value of the 5 TTMs.

This happenes to the slicer of "Tier 1 New, Tier 2.... " and the corresponding KPI card of "Sales by Product Categories"

When I choose "Select all" in the slicer of "Tier 1 New, Tier 2...." , the the KPI card above only displays the value of the last slicer item ,that is "X Product", but not the total value of all the slicer items.

Can any one help resolve this problem?

Thank you very much!

[Moana]

Hi @EthanChenJichao 

I was not able to access the file but seems like you need to check the data type of the filed you are pulling in the KPI.
If its a number, it will calculate the sum and show it you based on the slicer you select. But if its a text field it might show the first/last occurence.

Hope this helps!

Thanks

[EthanChenJichao]

@Moana Hi, I think you can access and edit the pbix file now. Thanks for your help.

[EthanChenJichao]

@Moana Hi, thanks for your sugggesition. I checked the data type, it is decimal number, not text. So the reason might not be the data type.  Can you tell me a way to upload the pbix file in this forum? Thanks!!

[AmiraBedh]

Can you please share your PBI file?

[EthanChenJichao]

@AmiraBedh Hi, I think you can edit the PBIX file now. Thanks for your help.

[Moana · Accepted solution]

Hi @EthanChenJichao ,

The KPI visual object in Power BI is intended to reflect performance against a target over a specific period or condition. When no specific filters are applied, it defaults to displaying the most recent period of data (in your case, the most recent year) as the metric. This is because KPIs are often used to monitor current performance against a goal which usually involves the most recent data available.

On the other hand, card visual aggregate data from all available data points unless otherwise filtered. This means that in the absence of any slicers affecting these visuals, they will show the sum of all years.

You can use the card visual in this case as it will show the correct sum.

Additionally, you can try using Running total measure-

Running sum= calculate([Sales],filter(all('Product category'[Invoice Date ]),'Product category'[Invoice Date ]<=max(('Product category'[Invoice Date ]))))

Note- Make the Invoice date in Date format from power query editor otherwise it will take it as text value.

Hope this helps!

[EthanChenJichao]

@Moana Hi, thanks for your explanation. I also found out that the Card visual is more suitable than KPI Card visual. 

[EthanChenJichao]

Hi @AmiraBedh I added the PBI file as a link in my question. Can you see it?