Create a card visual displaying count of table with specific condition

[wiliam_fjr]

I have the following table

Branch	Earnings	Expense	Quarter
A	40000	12000	Q1
A	13000	11000	Q2
B	25000	20000	Q1
B	10000	23000	Q2
C	12000	13000	Q1
C	13000	15000	Q2

I need to display a card visual showing a count of "healthy" branch (earnings > expenses), with a slicer showing the Quarter. I can create a card that show the data for 1 quarter, but when selecting multiple quarter, I need to make it so that I calculate the sum of earning and expenses for each branch on those quarters before displaying the count of healthy branch.
Is there a way of doing that?

[Adamboer]

Hope you are doing well. Based on the requirements you have shared, you can create a measure to calculate the count of "healthy" branches for the selected quarters. Here's an example of how you can do it:

1. Create a new measure and name it "Healthy Branches". Use the following formula:

   Healthy Branches = COUNTROWS(FILTER(SUMMARIZE('Table', 'Table'[Branch], "Total Earnings", SUM('Table'[Earnings]), "Total Expenses", SUM('Table'[Expense]), "Quarter", 'Table'[Quarter]), [Total Earnings] > [Total Expenses]))

2. Drag and drop the "Quarter" column from your table into the slicer visual.

3. Add a card visual to your report and drag the "Healthy Branches" measure into it.

When you select one or multiple quarters from the slicer, the card visual will display the count of healthy branches for the selected quarters.

I hope this helps. Let me know if you have any further questions or concerns.

[DOLEARY85]

Ah okay, so you need to factor in the quarter too, try:

Healthy = CALCULATE(DISTINCTCOUNT('Table (5)'[Branch]),'Table (5)'[Earnings]>'Table (5)'[Expense], 'Table (5)'[Quarter]=max('Table (5)'[Quarter]),all('Table (5)'))

Unhealthy =  CALCULATE(DISTINCTCOUNT('Table (5)'[Branch]),'Table (5)'[Earnings]<'Table (5)'[Expense], 'Table (5)'[Quarter]=max('Table (5)'[Quarter]),all('Table (5)'))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[wiliam_fjr]

Thanks for the answer, this one is closer to what i need. From my understanding this use the latest quarter as the displayed value whereas i need to calculate the sum of earning and expenses of each branch on a selected period then calculate the amount of healthy branch from there. If i dont get another answer within 24 hr i'll just accept this as solution instead

[DOLEARY85]

Hi,

try this:

Measure = CALCULATE(COUNT('Table (5)'[Branch]),'Table (5)'[Earnings]>'Table (5)'[Expense])

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[wiliam_fjr]

Thanks for the try, but that did not quite do it for example if i create the opposite of your measure to calculate "unhealthy" branch instead
Measure_2 = CALCULATE(COUNT('Table (5)'[Branch]),'Table (5)'[Earnings]<'Table (5)'[Expense])

if we select quarter 1 / quarter 2 independently, the value is correct and the total of branch will not exceed 3 (A,B,C) but when selecting all quarter using your measure, both measure1 and 2 displaying 3 branch each, totaling 6 which should not be possible because there are only 3 branch max