Generate series or table based on measures

Question

Hi!&nbsp;Below is my data for this report (text and photo provided).&nbsp;&nbsp;ReferenceActionCompany CodeDateMonthYearMerged900147675110Pick up500007761/3/202120210120210150000776900147675211Pick up500007761/3/202120210120210150000776900147675311Pick up500007761/3/202120210120210150000776900147675511Pick up500007761/3/202120210120210150000776900147674110Pick up500018421/3/2021202101202101500018425.00063E+12Served500063261/27/2021 9:58:36 PM202101202101500063265.00087E+12Served500086641/28/2021 5:09:08 AM202101202101500086645.00059E+12Served500058681/27/2021 8:07:13 PM202101202101500058685.0009E+12Served500090011/28/2021 4:24:48 AM202101202101500090015.0009E+12Served500090011/28/2021 9:47:48 PM202101202101500090015.00093E+12Served500092741/29/2021 11:44:58 PM202101202101500092745.0009E+12Served500089571/28/2021 5:09:09 AM202101202101500089575.00093E+12Served500092741/28/2021 5:09:08 AM202101202101500092745.00063E+12Served500063171/29/2021 1:14:52 AM202101202101500063175.00016E+12Served500016101/28/2021 2:30:15 AM202101202101500016105.0003E+12Served500030221/31/2021 11:44:40 PM202101202101500030225.0003E+12Served500030221/31/2021 11:24:25 PM20210120210150003022&nbsp;&nbsp;and here is my output matrix using the following measures:&nbsp;Count of Served = CALCULATE(COUNT(SharePoint[Action]),SharePoint[Action]="Served")

Count of PU = CALCULATE(COUNT(SharePoint[Action]),SharePoint[Action]="Pick up")

% of eATW = [Count of Served]/[Count of PU]

Additional Raffle = IF([% of eATW]=1, 4,0)

TotalRaffleTickets = [Count of Served] + [Additional Raffle]&nbsp;&nbsp;My desired additional table is below (column R in the photo). The generated rows (or series) are&nbsp; based on the total raffle tickets computed. FYI that the 'merged' column is combined columns of MonthYear and the Company Code.&nbsp;&nbsp;Hope somebody can help me!&nbsp;

amitchandak · Answer

newgirl , You will not able to generate a series of measures as this new table will not respond to the slicer values

newgirl · Answer

hi&nbsp;amitchandak&nbsp; oh, I see. Nevertheless, how to proceed with generating the desired series of measures even though it won't respond to slicer values?&nbsp;&nbsp;

amitchandak · Answer

newgirl&nbsp;,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

newgirl · Answer

Hi,&nbsp;amitchandak&nbsp;!&nbsp;Here is the link of sample data in sheet name 'sample'. In the 'pivot' sheet, it displays the desired output I need.&nbsp;https://drive.google.com/file/d/1f7gV47oqlX-WNKDL_hECUIHNL6kDKM1E/view?usp=sharing&nbsp;Thank you!

v-easonf-msft · Answer

Hi,&nbsp;newgirl&nbsp;
I am afraid it is difficult to&nbsp; use dax to expand multiple rows based on the value of a measure column.
&nbsp;
If you still have no idea about your problem, you can open a support ticket with MSFT to seek technical support , usually they will have better suggestions.
&nbsp;
Best Regards,Community Support Team _ Eason
&nbsp;
&nbsp;
&nbsp;

Forum Discussion

Generate series or table based on measures

5 Replies

Related content

Generate Series input value

Generate Series

Generate date column using generate series

Generating Missing Data

Generate Series

Reference	Action	Company Code	Date	MonthYear	Merged
900147675110	Pick up	50000776	1/3/2021	202101	20210150000776
900147675211	Pick up	50000776	1/3/2021	202101	20210150000776
900147675311	Pick up	50000776	1/3/2021	202101	20210150000776
900147675511	Pick up	50000776	1/3/2021	202101	20210150000776
900147674110	Pick up	50001842	1/3/2021	202101	20210150001842
5.00063E+12	Served	50006326	1/27/2021 9:58:36 PM	202101	20210150006326
5.00087E+12	Served	50008664	1/28/2021 5:09:08 AM	202101	20210150008664
5.00059E+12	Served	50005868	1/27/2021 8:07:13 PM	202101	20210150005868
5.0009E+12	Served	50009001	1/28/2021 4:24:48 AM	202101	20210150009001
5.0009E+12	Served	50009001	1/28/2021 9:47:48 PM	202101	20210150009001
5.00093E+12	Served	50009274	1/29/2021 11:44:58 PM	202101	20210150009274
5.0009E+12	Served	50008957	1/28/2021 5:09:09 AM	202101	20210150008957
5.00093E+12	Served	50009274	1/28/2021 5:09:08 AM	202101	20210150009274
5.00063E+12	Served	50006317	1/29/2021 1:14:52 AM	202101	20210150006317
5.00016E+12	Served	50001610	1/28/2021 2:30:15 AM	202101	20210150001610
5.0003E+12	Served	50003022	1/31/2021 11:44:40 PM	202101	20210150003022
5.0003E+12	Served	50003022	1/31/2021 11:24:25 PM	202101	20210150003022