cancel
Showing results for
Did you mean:
Frequent Visitor

## New Column for Totals and Cumulative totals

Hi,

I am new to Power BI. Learning on My own.I am attaching an excel  file  with some data inputs .

The columns highlighted in BOLD / Yellow are the new calculated colums I  want to create in table or query .(3 columns)

Explanation for Calculated columns as below.

Monthly Consumption - Sum Mothly values by each  Month for each country

Waste % - Waste  divided by monthly consumption .

Cumulative Waste % - Cumulative waste for each month and Country.

Regards

Joshi

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: New Column for Totals and Cumulative totals

This is generally not how you should be approaching this. Have a read of my article here https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Frequent Visitor

## Re: New Column for Totals and Cumulative totals

Hi,

Pasting the excel file.

Let me know how to go about it

Thanks

Regards

Joshi

 Date Country Material Wate Consumption Monthly Consumption Monthly Waste% Cumulative  Waste% 1/1/2019 Brazil A 200 1200 3200 6% 6% 1/1/2019 Brazil B 100 1000 3200 3% 9% 1/1/2019 Brazil C 100 1000 3200 3% 13% 1/1/2019 China A 200 1100 3500 6% 6% 1/1/2019 China B 100 1000 3500 3% 9% 1/1/2019 China C 200 1400 3500 6% 14% 1/1/2019 India A 100 1000 3200 3% 3% 1/1/2019 India B 200 1200 3200 6% 9% 1/1/2019 India C 200 1000 3200 6% 16% 2/1/2019 Brazil A 300 1400 3900 8% 8% 2/1/2019 Brazil B 200 1200 3900 5% 13% 2/1/2019 Brazil C 100 1300 3900 3% 15% 2/1/2019 China A 200 1300 3900 5% 5% 2/1/2019 China B 100 1500 3900 3% 8% 2/1/2019 China C 200 1100 3900 5% 13% 2/1/2019 India A 200 1500 3900 5% 5% 2/1/2019 India B 200 1200 3900 5% 10% 2/1/2019 India C 100 1200 3900 3% 13% 3/1/2019 Brazil A 200 1300 3900 5% 5% 3/1/2019 Brazil B 200 1200 3900 5% 10% 3/1/2019 Brazil C 100 1400 3900 3% 13% 3/1/2019 China A 200 1400 3700 5% 5% 3/1/2019 China B 300 1100 3700 8% 14% 3/1/2019 China C 200 1200 3700 5% 19% 3/1/2019 India A 300 1000 3300 9% 9% 3/1/2019 India B 200 1200 3300 6% 15% 3/1/2019 India C 100 1100 3300 3% 18%
8 REPLIES 8
Super User

## Re: New Column for Totals and Cumulative totals

This is generally not how you should be approaching this. Have a read of my article here https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User

## Re: New Column for Totals and Cumulative totals

Hi,

Writing measures would be better.  Share the link from where i can download your PBI file.  Alternatively paste data here such that i can paste it in an Excel workbook.

Frequent Visitor

## Re: New Column for Totals and Cumulative totals

Hi,

Pasting the excel file.

Let me know how to go about it

Thanks

Regards

Joshi

 Date Country Material Wate Consumption Monthly Consumption Monthly Waste% Cumulative  Waste% 1/1/2019 Brazil A 200 1200 3200 6% 6% 1/1/2019 Brazil B 100 1000 3200 3% 9% 1/1/2019 Brazil C 100 1000 3200 3% 13% 1/1/2019 China A 200 1100 3500 6% 6% 1/1/2019 China B 100 1000 3500 3% 9% 1/1/2019 China C 200 1400 3500 6% 14% 1/1/2019 India A 100 1000 3200 3% 3% 1/1/2019 India B 200 1200 3200 6% 9% 1/1/2019 India C 200 1000 3200 6% 16% 2/1/2019 Brazil A 300 1400 3900 8% 8% 2/1/2019 Brazil B 200 1200 3900 5% 13% 2/1/2019 Brazil C 100 1300 3900 3% 15% 2/1/2019 China A 200 1300 3900 5% 5% 2/1/2019 China B 100 1500 3900 3% 8% 2/1/2019 China C 200 1100 3900 5% 13% 2/1/2019 India A 200 1500 3900 5% 5% 2/1/2019 India B 200 1200 3900 5% 10% 2/1/2019 India C 100 1200 3900 3% 13% 3/1/2019 Brazil A 200 1300 3900 5% 5% 3/1/2019 Brazil B 200 1200 3900 5% 10% 3/1/2019 Brazil C 100 1400 3900 3% 13% 3/1/2019 China A 200 1400 3700 5% 5% 3/1/2019 China B 300 1100 3700 8% 14% 3/1/2019 China C 200 1200 3700 5% 19% 3/1/2019 India A 300 1000 3300 9% 9% 3/1/2019 India B 200 1200 3300 6% 15% 3/1/2019 India C 100 1100 3300 3% 18%
Super User

## Re: New Column for Totals and Cumulative totals

Hi,

You may download my PBI file from here.  I cannot figure out how to compute the last measure for now.

Hope this helps.

Highlighted
Super User

## Re: New Column for Totals and Cumulative totals

Here is the formula for cumulative waste %

```Cumulative Monthly Waste % =
DIVIDE (
CALCULATE (
SUM ( Data[Wate ] ),
FILTER ( ALL ( Data[Material] ), Data[Material] <= MAX ( Data[Material] ) )
),
[Monthly Consumption]
)
```

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Frequent Visitor

## Re: New Column for Totals and Cumulative totals

Hi,

Sorry for the late respone.

Solution Understood.

Wanted to check How can i get a column for YTD consumption per Country

Thanks

Joshi

Frequent Visitor

## Re: New Column for Totals and Cumulative totals

Thanks. Solution understood.

Sorry for the late response.

Thanks

Joshi

Super User

## Re: New Column for Totals and Cumulative totals

There are functions for that, but you need a calendar table. Read about it here https://exceleratorbi.com.au/dax-time-intelligence-beginners/

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.