topic dividing row total of a column by the subtotal of that column, period wise in DAX Commands and Tips

dividing row total of a column by the subtotal of that column, period wise

Anonymous — Wed, 08 Nov 2023 12:48:27 GMT

i want to divide each row of TY_Actual with its subtotal i.e 110679.54 to get the %. for example if i divide lunch row TY i.e 30001.88 by 110679.54 i will get 27%. now here is the catch, this TY_Actual column has data of 6 months, and the value 30001.88 is the sum of values of 6 months. now if i put a slicer on it for 5th month, my TY_Actual col will be updated with 5th month data and subtotal will change and thus the % calculated will change too.

i'm currently using this dax query ----

TTP by Total % =

var a = sum('Daypart'[TTP_TY_Actual])

var b = calculate ( sum('Daypart'[TTP_TY_Actual]),REMOVEFILTERS('Daypart'))

return

divide(a,b) * 100

Re: dividing row total of a column by the subtotal of that column, period wise

edhans — Wed, 08 Nov 2023 14:11:18 GMT

You can restore the months in the filter as shown below with VALUES().

TTP by Total % = VAR a = SUM( 'Daypart'[TTP_TY_Actual] ) VAR b = CALCULATE( SUM( 'Daypart'[TTP_TY_Actual] ), REMOVEFILTERS( 'Daypart' ), VALUES( DayPart[YearMonth] ) ) RETURN DIVIDE( a, b ) * 100

However, your request isn't 100% clear, so please explain your exact goal.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

Re: dividing row total of a column by the subtotal of that column, period wise

Anonymous — Thu, 09 Nov 2023 06:20:59 GMT

yes it is working. actually i want to make it more dynamic. i have other columns like TY_1, TY_2 and so on so how can i generalize your formula to make it more dynamic

Re: dividing row total of a column by the subtotal of that column, period wise

edhans — Thu, 09 Nov 2023 16:09:00 GMT

Replace REMOVEFILTERS() with ALLSELECTED().

That might not do what you want though. You didn't explain what you mean by "Dynamic." Please read the below this time give us enough info to help.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.