Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
roboskoog
Frequent Visitor

Summarizing monthly data

Hello All -

 

I've been struggling with creating a monthly summary of defects detected.  My table has Project ID, Application ID, Month and Discovered defects.  I need to summarize the discovered defects by application and month.  Since an application can be tied to multiple projects in a month, I need Project ID to filter which applications should be included in the display.

 

I've tried mulitple things and cannot get a monthly total by application, including SUMMARIZE, SUM, SUMX, GROUPBY, SUMMARIZECOLUMNS.  Usually I get the message: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."  I'm thinking this is because I do not including the Project ID in the calculation.  

 

Any suggestions on how to get monthly totals by application?

1 ACCEPTED SOLUTION

Using the suggested posting, I was able to get this to work.  Actual code is:

 

SUMX(CROSSJOIN(values('Reliability by Appllication (2)'[Appl_ID]),values('Reliability by Appllication (2)'[Reporting Month])),CALCULATE(SUM('Reliability by Appllication (2)'[Discovered Defects])))

 

I now get a monthly total of defects by application.

 

Thanks for the suggestion.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@roboskoog,

There seems to be a similar issue in this thread: https://community.powerbi.com/t5/Desktop/SUMMARIZE-error-The-expression-refers-to-multiple-columns-c...  Does that help?

 

Failing that, can you post the measure that is giving you grief, and an idea of how you want to use it in a visual - e.g. table/ matrix/ card, columns/rows you need ?

 

Cheers.

Using the suggested posting, I was able to get this to work.  Actual code is:

 

SUMX(CROSSJOIN(values('Reliability by Appllication (2)'[Appl_ID]),values('Reliability by Appllication (2)'[Reporting Month])),CALCULATE(SUM('Reliability by Appllication (2)'[Discovered Defects])))

 

I now get a monthly total of defects by application.

 

Thanks for the suggestion.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.