topic Re: Subtotals in Matrix needed in DAX in DAX Commands and Tips

Subtotals in Matrix needed in DAX

ariana_night — Mon, 21 Aug 2023 19:29:27 GMT

Hello,

I am trying to get the same subtotals that are in my matrix into some Dax measures so I can then compare periods and create variance measures. I need to have month to date, previous month, year to date, and previous year.

Example: Month to date is currently showing subtotal of 82.28% for Frozen Units Filled %. I need to get this same result from a Dax calculation.

Same for last month: previous month subtotal is 83.49%. I would then create a measure to get the variance (I know this part already).

- [ReqAvailableDate] is connected to field [Week Date] in a separate Calendar table.

- [Frozen Units Filled %] is a measure I created.

I appreciate your help. Please let me know if you need additional information.

Re: Subtotals in Matrix needed in DAX

Greg_Deckler — Mon, 21 Aug 2023 20:30:41 GMT

ariana_night Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Subtotals in Matrix needed in DAX

ariana_night — Thu, 24 Aug 2023 15:38:58 GMT

I hope this is the information you need...

Below table is a sample with aggregates for Frozen Units Filled each day in the previous month.

(Frozen Units Filled% is a dax measure = (DIVIDE(CALCULATE(SUM('FrozenFillRate'[Confirmed Sales Quantity])), CALCULATE(SUM('FrozenFillRate'[Order Quantity])))

I need to create a measure that will give me the total of the Frozen Units Filled % in the previous month, which would be 83.55% (as generated when the data is placed in a matrix).

Year - Month	ReqAvailableDate	Frozen Units Filled %
2023-7	7/1/2023	77.17%
2023-7	7/2/2023	85.03%
2023-7	7/3/2023	86.17%
2023-7	7/4/2023	85.53%
2023-7	7/5/2023	81.55%
2023-7	7/6/2023	81.36%
2023-7	7/7/2023	84.12%
2023-7	7/8/2023	79.61%
2023-7	7/9/2023	73.15%
2023-7	7/10/2023	83.80%
2023-7	7/11/2023	87.74%
2023-7	7/12/2023	84.04%
2023-7	7/13/2023	85.61%
2023-7	7/14/2023	84.52%
2023-7	7/15/2023	79.94%
2023-7	7/16/2023	79.28%
2023-7	7/17/2023	82.48%
2023-7	7/18/2023	85.45%
2023-7	7/19/2023	84.60%
2023-7	7/20/2023	85.22%
2023-7	7/21/2023	84.53%
2023-7	7/22/2023	81.72%
2023-7	7/23/2023	82.87%
2023-7	7/24/2023	82.00%
2023-7	7/25/2023	78.14%
2023-7	7/26/2023	87.45%
2023-7	7/27/2023	87.61%
2023-7	7/28/2023	81.31%
2023-7	7/29/2023	80.55%
2023-7	7/30/2023	86.06%
2023-7	7/31/2023	82.67%

Re: Subtotals in Matrix needed in DAX

ariana_night — Thu, 24 Aug 2023 17:56:28 GMT

Greg_Deckler forgot to tag you