Forum Discussion
diving subtotal with each category
starting from left to right - 1st column is Period 2nd is type then , time period and month 1 and month 2 data.
i want a dax formula to divide lunch total of month 1 by total of lunch and afternoon. similarly for month 2.
FOR EXAMPLE : let sum of Lunch be 100 and sum of afternoon be 50 for month 1, so formula should divide 100/150, and similarly 50/150. basically i want each type's contribution to the total.
3 Replies
Hi Anonymous ,
First you'll want to get your data into the optimum structure for SSAS reporting.
In Power Query, multi-select (Ctrl+click) your [Period], [Type], and [Time Period] columns, then go to the Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns. This will give you an [Attribute] column with your months names in (from the old column names) and a [Value] column with all the old column values in.
Once you send the restructured data to your data model, you can write a measure like this:
_lunchPctOfTotal = VAR __total = SUM(yourTable[Value]) VAR __lunch = CALCULATE( SUM(yourTable[Value]), yourTable[Type] = "Lunch" ) RETURN DIVIDE(__lunch, __total, 0)You can then put this measure into visuals along with your new [Attribute] column (and any other dimensions) to contextually see the lunch % of total value.
Pete
Power Query is part of Power BI and the measure is in DAX.
Can you be a bit more specific about what's not working for you please?
Pete
