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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Joshi_M
Helper I
Helper I

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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

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%

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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%

Hi,

Sorry for the late respone.

Solution Understood.

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

 

Thanks

Joshi

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

 

Thanks. Solution understood.

Sorry for the late response.

 

Thanks

Joshi

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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