Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Sum/Count of the output from a if statement

Hi all,

 

I have created a report that contain sales, where I also have tried to include some low-level customer retention analysis.

 

Intro:

 

1)First, I have a measure for the sum of sales quantity.

 

Quantity = sum('Sales'[Qty])

 

2)Then I have created a series from 1-12, using "New parameter", that can be used in a slicer to filter until 12 months back from todays date.

 

Period = Generateseries(1,12,1)

 

3)Then I have created a measure for the sum of sales quantity dependent on the chosen value in the slicer mentioned above.

 

Quantity Last Period =

Calculate(Quantity,

Datesinperiod(

                        'Date'[Date],

                        Lastdate('Date'[Date]),

                        -1*[Period Value],

                        MONTH)

)

 

Where the measure Period Value is calcualated: Period Value = Selectedvalue('Period'[Period],3)

 

4)Then, if there is a value for sales in the chosen period, a measure is returning 0, if there is a blank value, it returns 1.

 

Lost Customer = if(Quantity Last Period,0,1)

 

 

So, to the issue:

 

The measure in (4) return either a 0 or a 1 depending on the customer is lost or not. But, since the measure contain an if statement, the calculation is also evaluated for the total, where the value for Quantity Last Period is never blank, thus as a total, there are no lost customers. What I would like is to see the sum/count of lost customers for each period chosen in the slicer. Whether or not this is shown in the table or in a card is not important, I just want to see total lost customers.

 

The measure is shown in a table, where the customer number is located at the left most column, and then the measures for Quantity, Qyantity Last Period, and Lost Customers is shown.

 

I have tried to look through the forum for answers on this, but either I do not understand, or I have not managed to find a relevant post for this.

 

Any help is highly appreciated.

 

 

2 REPLIES 2
speedramps
Super User
Super User

Hi Marius

 

Please consider this solution and leave kudos.

Try use it in a stack chart column with a monthyear x-axis.

For each bar in the chart, the measure will compare the 'current' bar with the future bars.

If a customer has sales in a period but not any afterwards, then it must have been lost in that period. 🙂

This is quicker and simpler than checking quatities.

 

Number of lost customers=


VAR maxdate = MAX('Calendar'[Date])

 

VAR currentcustomers=
VALUES ( FactSales[CustomerID])

 

VAR futurecustomers=
CALCULATETABLE (
VALUES ( FactSales[CustomerID]),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] > maxdate))

 

VAR lostcustomers =
EXCEPT(currentcustomers,futurecustomers)

 

RETURN
COUNTROWS(lostcustomers)

Anonymous
Not applicable

Hi @speedramps ,

 

Although it did not solve my specific issue, you did provide me with an alternative solution that by the looks of it might be just as good. Thank you!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors