Power BI Total for category (filter not applied)

Question

Total of sales values is 3000 and I want to show total value when I select a, b or c. when I select a or b , it gives total value as 3000. but when I select c, it doesn't show total value, it shows as 2600. the reason is 1st of July no value for C. so when I select c, it doesn't take 1st of July values for calculation. Since a & b have values for all days, when I select a or b, it shoes total value as 3000. How can I avoid this error?

Answer 1

Hello there,

It seems like you're asking how to calculate the total for a specific category in Power BI when no filters are applied. To achieve this, you can use DAX (Data Analysis Expressions) formulas within Power BI. Here's a step-by-step guide on how you could approach this:

Let's assume you have a table named "Sales" with columns "Category" and "Amount" that represent your sales data.

Create a Measure for Total Sales:

In the "Model" view of Power BI, go to the "Modeling" tab and click on "New Measure." Name the measure something like "Total Sales."

Total Sales = SUM(Sales[Amount])

This measure will calculate the total sales amount across all categories.

Create a Measure for Total Sales by Category:

Now, create another measure that calculates the total sales for a specific category. This measure will allow you to see the total for a category even when no filters are applied.

Total Sales by Category =

CALCULATE([Total Sales], ALL(Sales[Category]))

The ALL(Sales[Category]) function removes any filters applied to the Category column, ensuring that you get the total for the selected category regardless of any other filters.

Visualize the Total Sales by Category:

In the "Report" view, create a visual, such as a card or a table, and use the "Total Sales by Category" measure in that visual. This will display the total sales for the selected category, regardless of other filters applied.

I used AI provided by ChatGPT to formulate part of this response. I have verified that the information is accurate before sharing it with you.

Hope this resolves your Query !!

--If the reply is helpful, please Upvote and Accept it as an answer–

Answer 2

If I don't select any category, it show correct values. If I select "a", still show correct values as "a" is available all days, but when I select "b" or "c", percentage values are wrong. reason is "b" & "c" are not in all days. If I select "b" , it wont calculate 17th day values for calculation. how can I fix this issue