About power bi

[KhushiYadav253]

Hi,
I have one table in power bi called 'Groceries' which have following columns: 
Date,Fruits, City Name, Person Name.
Fruit column has 4 categories : Apple, Grapes, Guava, Pineapple


I want line chart that show count of rows for each category of fruits excluding 'Pineapple' , with slicers for Date,Fruits, City Name, Person Name, and aslo when one of the Fruit selected in slicer, it should show that category count and also total of that 3 categories count.


So i have created measures in 'Groceries' like this for all three categories: 

Abandon = CALCULATE(COUNTROWS('Groceries),('Groceries'[Fruits]="Apple")

And i have created summarized table like : 

SUMMARIZECOLUMNS (
    'Groceries' [Date],
    "Total Apple", CALCULATE(COUNTROWS('Groceries'),'Groceries'[Fruits] = "Apple" ),
    "Total Guava", CALCULATE(COUNTROWS('Groceries'),'Groceries'[Fruits] = "Guava"),
    "Total Grapes", CALCULATE(COUNTROWS('Groceries'),'Groceries'[Fruits] = "Grapes") ,
    "Total", CALCULATE(COUNTROWS('Groceries'),'Groceries'[Fruits] = "Apple" || 'Groceries'[Fruits] = "Guava" || 'Groceries'[Fruits] = "Grapes" )


In line chart, on Y-axis its : 3 measures of categories from 'Groceries' table and Total Column of Summarize Table.
But it not changes value of Total column With value of slicers.

Note : Groceries and Summarized table both have many to one relationship with date column of date_dimension table.

How to achive this ?

[rajendraongole1]

Hi @KhushiYadav253 - Instead of creating a separate summarized table, you can write dax measures in your existing Groceries table it updates on slicer selections.

Create a separate measure for each of the three fruit categories

Total Apple =
CALCULATE(COUNTROWS('Groceries'), 'Groceries'[Fruits] = "Apple")

Total Guava =
CALCULATE(COUNTROWS('Groceries'), 'Groceries'[Fruits] = "Guava")

Total Grapes =
CALCULATE(COUNTROWS('Groceries'), 'Groceries'[Fruits] = "Grapes")

Create a measure that dynamically calculates the total count for the selected fruit categories excluding 'Pineapple'. This measure will be sensitive to the slicer selection.

Total Selected Fruits =
VAR SelectedFruits = VALUES('Groceries'[Fruits])
RETURN
CALCULATE(
COUNTROWS('Groceries'),
'Groceries'[Fruits] IN {"Apple", "Guava", "Grapes"} &&
(ISFILTERED('Groceries'[Fruits]) = FALSE || 'Groceries'[Fruits] IN SelectedFruits)
)

you can add x-axis with Date column from the Date table.

Y-Axis with add the measures Total Apple, Total Guava, Total Grapes, and Total Selected Fruits.
Slicers: Add slicers for Date, Fruits, City Name, and Person Name.

Hope it works

[KhushiYadav253]

Thanks for the quick response.
It is not working well in my scenario.
Wanted to note that i have page level filter of Person_Name,DayOfWeek (From DateDimension Table), and have by default selected some values from slicer of City_Name.

I have tried this fromula but it not showing correct sum of three ctegories.

I want this Total_Selected_Fruit Measure to be affect by all filters and slicer values on the page, but when i select any of the Fruit category from Fruits slicer, it should show a line for Total of three category and total of that one selected category along with filters applied of page level.

How to do that?