cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
newgirl
Helper IV
Helper IV

Generate series or table based on measures

Hi! 

Below is my data for this report (text and photo provided).

 

eATW 2.JPG

 

ReferenceActionCompany CodeDateMonthYearMerged
900147675110Pick up500007761/3/202120210120210150000776
900147675211Pick up500007761/3/202120210120210150000776
900147675311Pick up500007761/3/202120210120210150000776
900147675511Pick up500007761/3/202120210120210150000776
900147674110Pick up500018421/3/202120210120210150001842
5.00063E+12Served500063261/27/2021 9:58:36 PM20210120210150006326
5.00087E+12Served500086641/28/2021 5:09:08 AM20210120210150008664
5.00059E+12Served500058681/27/2021 8:07:13 PM20210120210150005868
5.0009E+12Served500090011/28/2021 4:24:48 AM20210120210150009001
5.0009E+12Served500090011/28/2021 9:47:48 PM20210120210150009001
5.00093E+12Served500092741/29/2021 11:44:58 PM20210120210150009274
5.0009E+12Served500089571/28/2021 5:09:09 AM20210120210150008957
5.00093E+12Served500092741/28/2021 5:09:08 AM20210120210150009274
5.00063E+12Served500063171/29/2021 1:14:52 AM20210120210150006317
5.00016E+12Served500016101/28/2021 2:30:15 AM20210120210150001610
5.0003E+12Served500030221/31/2021 11:44:40 PM20210120210150003022
5.0003E+12Served500030221/31/2021 11:24:25 PM20210120210150003022

 

 

and here is my output matrix using the following measures:

 

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]

 

eATW 1.JPG

 

My desired additional table is below (column R in the photo). The generated rows (or series) are  based on the total raffle tickets computed. FYI that the 'merged' column is combined columns of MonthYear and the Company Code. 

 

Hope somebody can help me!

eATW 3.JPG

 

5 REPLIES 5
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi, @amitchandak !

 

Here is the link of sample data in sheet name 'sample'. In the 'pivot' sheet, it displays the desired output I need.

 

https://drive.google.com/file/d/1f7gV47oqlX-WNKDL_hECUIHNL6kDKM1E/view?usp=sharing

 

Thank you!

Hi, @newgirl 

I am afraid it is difficult to  use dax to expand multiple rows based on the value of a measure column.

 

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.

 

Best Regards,
Community Support Team _ Eason

 

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors