cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joshi_M Frequent Visitor
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.

Please suggest ways to resolve.

Regards

Joshi

Example.jpg

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
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.
Joshi_M Frequent Visitor
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 CountryMaterialWate ConsumptionMonthly ConsumptionMonthly Waste%Cumulative  Waste%
1/1/2019BrazilA200120032006%6%
1/1/2019BrazilB100100032003%9%
1/1/2019BrazilC100100032003%13%
1/1/2019ChinaA200110035006%6%
1/1/2019ChinaB100100035003%9%
1/1/2019ChinaC200140035006%14%
1/1/2019IndiaA100100032003%3%
1/1/2019IndiaB200120032006%9%
1/1/2019IndiaC200100032006%16%
2/1/2019BrazilA300140039008%8%
2/1/2019BrazilB200120039005%13%
2/1/2019BrazilC100130039003%15%
2/1/2019ChinaA200130039005%5%
2/1/2019ChinaB100150039003%8%
2/1/2019ChinaC200110039005%13%
2/1/2019IndiaA200150039005%5%
2/1/2019IndiaB200120039005%10%
2/1/2019IndiaC100120039003%13%
3/1/2019BrazilA200130039005%5%
3/1/2019BrazilB200120039005%10%
3/1/2019BrazilC100140039003%13%
3/1/2019ChinaA200140037005%5%
3/1/2019ChinaB300110037008%14%
3/1/2019ChinaC200120037005%19%
3/1/2019IndiaA300100033009%9%
3/1/2019IndiaB200120033006%15%
3/1/2019IndiaC100110033003%18%
8 REPLIES 8
Super User
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
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.

Joshi_M Frequent Visitor
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 CountryMaterialWate ConsumptionMonthly ConsumptionMonthly Waste%Cumulative  Waste%
1/1/2019BrazilA200120032006%6%
1/1/2019BrazilB100100032003%9%
1/1/2019BrazilC100100032003%13%
1/1/2019ChinaA200110035006%6%
1/1/2019ChinaB100100035003%9%
1/1/2019ChinaC200140035006%14%
1/1/2019IndiaA100100032003%3%
1/1/2019IndiaB200120032006%9%
1/1/2019IndiaC200100032006%16%
2/1/2019BrazilA300140039008%8%
2/1/2019BrazilB200120039005%13%
2/1/2019BrazilC100130039003%15%
2/1/2019ChinaA200130039005%5%
2/1/2019ChinaB100150039003%8%
2/1/2019ChinaC200110039005%13%
2/1/2019IndiaA200150039005%5%
2/1/2019IndiaB200120039005%10%
2/1/2019IndiaC100120039003%13%
3/1/2019BrazilA200130039005%5%
3/1/2019BrazilB200120039005%10%
3/1/2019BrazilC100140039003%13%
3/1/2019ChinaA200140037005%5%
3/1/2019ChinaB300110037008%14%
3/1/2019ChinaC200120037005%19%
3/1/2019IndiaA300100033009%9%
3/1/2019IndiaB200120033006%15%
3/1/2019IndiaC100110033003%18%
Super User
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.

Untitled.png

Highlighted
Super User
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.
Joshi_M Frequent Visitor
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

Joshi_M Frequent Visitor
Frequent Visitor

Re: New Column for Totals and Cumulative totals

 

Thanks. Solution understood.

Sorry for the late response.

 

Thanks

Joshi

Super User
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.