New Column for Totals and Cumulative totals

[Joshi_M]

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

[MattAllington · Accepted solution]

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/

[Ashish_Mathur]

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 · Accepted solution]

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%

[Joshi_M]

Hi,

Sorry for the late respone.

Solution Understood.

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

Thanks

Joshi

[MattAllington]

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

[Ashish_Mathur]

Hi,

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

Hope this helps.

[MattAllington]

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]
)

[MattAllington · Accepted solution]

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/

[Joshi_M]

Thanks. Solution understood.

Sorry for the late response.

Thanks

Joshi