Create customer list

[Anonymous]

Hi,

I'm relatively new to power bi and I'm hoping to create a customer analysis table.

I have a list of customers on a monthly basis like the picture below.

The excel file is at this link https://www.dropbox.com/s/8qgniy3tzl2y3a7/Customer%20list.xlsx?dl=0

How can I use power BI to present the table like so if I were to use excel:

I have created a date table and this is linked to the month end date in the data file.

Anyone has any idea?

Thanks

[v-lionel-msft]

Hi @Anonymous ,

Number of customers = COUNT('Sheet1 (3)'[Customer name]) 
Total sales = SUM('Sheet1 (3)'[Sales]) 

About [Variance], please give me a mathematical formula or refer to the answer.

Variance DAX 

For the second table, it cannot be fully realized in Power BI, the following is the closest effect:

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

The first table where you have shown measure on row. In power Bi matrix visual has an option in format. under "values" ->Show on Row. That will show on rows.

For the color, you can create measure based on datesytd  current, next and last month

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-1,MONTH))))

next MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],1,MONTH)))
next MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],1,MONTH))))

You those to create a formula/s that return color

Color = SWITCH ( TRUE(), [C1] <= 0, "Blue", [C1] >=0, "Yellow", [C1] <= 0, "Red", "Green" )

or if( isblank(Last MTD), "yellow", if([MTD]-[LMT])>0, )....

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601