Table Visualization by Customer breakdown by Type and Month

[Anonymous]

Hello Power BI Community

Can anyone please advise me on how to resolve this.

My use-case is that we have monthly finance transactional data for each month from Jan to May (e.g. Data source is SQL), bringing in the latest data every month. My Datasets each row consists of Month, Customer, Brand, Sales Price, and Volume and Data model consist of DateTable, where the month has one to many relationships with Customer Table. I manage to create DAX showing MTD & YTD calculation for [Volume], [GM], [ARP £/MT]. Please note [Volume], [GM], [ARP £/MT] these are measures created from monthly finance transactional data. I tried Summarise and Add column DAX nothing seems to be working out, may be i'm made some error. 

DAX Measures for Volume = Sum('Gross Margin'[Volume])
DAX Measure for ARP£/MT = (DIVIDE([Sale Price],[Volume]))

My final output would like to plot the data on a column chart by Type (Volume], ARP £/MT], [GM £/T] the columns would be stacked in each month to give the break-down by the customer by Brand. 

Table Chart Visualization format

I want to create two table visualization one by MTD and another Cumulative total, without giving the grand total for ARP £/MT (Average Retail Price) & GM £/ MT, something like this (above mentioned format).

[v-shex-msft]

HI @Anonymous,

AFAIK, you cant directly aggregate with measure formulas. I'd like to suggest you take a look at the following line about measure total level calculations to use if statement to check the current row contents level and write total level formulas for calculating.
Measure Totals, The Final Word  
Regards,

Xiaoxin Sheng

[MFelix]

Hi @Anonymous ,

You don't refer but I assume you have a year column somewhere in the data correct?

I would create a date column with end of month date to make the connection to calendar table using a date column.

This will allow you to make time inteliggence calculations (MTD, QTD, YTD).

Can you share a sample data?

[Anonymous]

Thanks @MFelix for reaching out to me.

Please find attach a sample PBIX file with Gross Margin and Datetable with Y2020 data only and all the DAX measure created - 

https://drive.google.com/file/d/1aEJ1vpmqG2TXv1-8CpAIyiepxSxKETIa/view?usp=sharing

Also explained below the final output views, please note this is sample data (Data hirerarchy Category-->>Customer) and not properly aggregated, just created to give an overview on how the final output should look like. Appreciate your help in advance