Need help with DAX

[JenniferWallace]

I want to create table like this.  How will we be able to achieve this using DAX? 

@Dangar332 

Name	dollar	Weeks	dollars/week
Holiday Weeks	$5	5	12.34
Non-Holiday Weeks	$4.9	5	12.34

[Dangar332]

Hi, @JenniferWallace 

you want it from manual data entry or based on another table?

if it based on manual entry then try @FreemanZ  solution 

if based on another table then try below

newtable =
   addcolumn(
       all(tablename[holidayweekcolumn]),
       "dollar" ,calculate(sum(tablename[dollarcolumn])),
       "weeks",weeknum(tablename[datecolumn],2),
       "dollar/week",calculate(sum(tablename[dollar/weekcolumn]))   
   )

if it not work then ellaborate your problem

[AnalyticPulse]

can you tell us what kind of column and table you have in your data

[FreemanZ]

hi @JenniferWallace ,

do you mean a calculated table like:

table = 
DATATABLE (
    "Name",STRING,
    "dollar", CURRENCY,
    "Weeks", INTEGER,
    "dollars/week", DOUBLE,
    {
        { "Holiday Weeks", 5, 5, 12.34},
        { "Non-Holiday Weeks", 4.9, 5, 12.34}
    }
)

it worked like:

[123abc]

To create a table like the one you've described using DAX (Data Analysis Expressions), you can use DAX measures in Power BI or other similar tools. Assuming you have a table in your data model that contains information about different types of weeks (e.g., "Holiday Weeks" and "Non-Holiday Weeks"), you can follow these steps:

1. Create a New Table: Create a new table in Power BI or your data modeling tool where you can define the structure of your table. You can use the following DAX formula to create a new table:

SummaryTable = VALUES('YourWeeksTable'[WeekType])

1. Replace 'YourWeeksTable' with the actual name of your table containing information about different weeks.

2. Define DAX Measures: Define the following DAX measures to calculate the desired values:

TotalDollars = SUM('YourWeeksTable'[Dollar])

TotalWeeks = SUM('YourWeeksTable'[Weeks])

DollarsPerWeek = DIVIDE([TotalDollars], [TotalWeeks], 0)

To create a table like the one you've described using DAX (Data Analysis Expressions), you can use DAX measures in Power BI or other similar tools. Assuming you have a table in your data model that contains information about different types of weeks (e.g., "Holiday Weeks" and "Non-Holiday Weeks"), you can follow these steps:

1. Create a New Table: Create a new table in Power BI or your data modeling tool where you can define the structure of your table. You can use the following DAX formula to create a new table:

   daxCopy code

   SummaryTable = VALUES('YourWeeksTable'[WeekType])

   Replace 'YourWeeksTable' with the actual name of your table containing information about different weeks.

2. Define DAX Measures: Define the following DAX measures to calculate the desired values:

   daxCopy code

   TotalDollars = SUM('YourWeeksTable'[Dollar]) TotalWeeks = SUM('YourWeeksTable'[Weeks]) DollarsPerWeek = DIVIDE([TotalDollars], [TotalWeeks], 0)

   Adjust the column and table names based on your actual data model.

3. Build the Table: Now, you can use these measures in your new table. Assuming you have a column named WeekType in your SummaryTable, add the following columns:

Name = 'SummaryTable'[WeekType]

Dollar = CALCULATE([TotalDollars], FILTER('YourWeeksTable', 'YourWeeksTable'[WeekType] = 'SummaryTable'[WeekType]))

Weeks = CALCULATE([TotalWeeks], FILTER('YourWeeksTable', 'YourWeeksTable'[WeekType] = 'SummaryTable'[WeekType]))

DollarsPerWeek = CALCULATE([DollarsPerWeek], FILTER('YourWeeksTable', 'YourWeeksTable'[WeekType] = 'SummaryTable'[WeekType]))

1. These columns will retrieve the necessary values for each week type.

Remember to replace 'YourWeeksTable' with the actual name of your table containing the week information.

This way, you will have a table with the desired structure, showing the total dollars, total weeks, and dollars per week for each week type.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.