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
tmyasoutov
Frequent Visitor

Measure to DIVIDE two columns with filtered values

Hey everyone,

 

I'm struggling to understand how to create a measure for a gauge/KPI visual for my report, having an execution ID (snag on product) and case name (production units inspected).

 

I need to divide execution ID (for example 400) and disctinct count case name ( production units inspected, for example 10). To have a view of 40 snags per unit this month. But also to be able to filter them by last month (for example October for now, but later Novermber without updating the report, since it's for management, rather than having slicers for an analytical report). And also to apply a filter for severity (high, medium, low) to have three gauges/KPIs.

 

All these columns are in one table as a source.

1 ACCEPTED SOLUTION

Hi @tmyasoutov ,

maybe so

Measure = COALESCE(DIVIDE(COUNT(Table[Execution ID]),DISTINCTCOUNT('Table'[Case Name])),0)

                             

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

                                

View solution in original post

6 REPLIES 6
mangaus1111
Solution Sage
Solution Sage

Hi @tmyasoutov ,

 

try with this

Measure = DIVIDE(COUNT(Table[Execution ID]),DISTINCTCOUNT('Table'[Case Name]))
 
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Understood, thank you @mangaus1111 ! Any idea how to correctly add IF(ISBLANK) = 0 within this measure?

Hi @tmyasoutov ,

maybe so

Measure = COALESCE(DIVIDE(COUNT(Table[Execution ID]),DISTINCTCOUNT('Table'[Case Name])),0)

                             

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

                                

Yes, just checked, it works! Thank you @mangaus1111 !

tmyasoutov
Frequent Visitor

Execution IDCase NameDateSeverity
445671001009811.09.2022Low
445681001009811.09.2022Low
445691001009811.09.2022Low
445701001009811.09.2022Low
445711001009912.09.2022High
445721001010013.09.2022Medium
445731001010013.09.2022High
445741001010114.09.2022Medium
445751001010214.09.2022Medium

 

Execution ID is an craftmanship error, Case Name is produced unit, date and severity. Count for Execution ID (10 in this case), distinct count for Case Name (5 in this case). I would like to show, that in September or other month, Execution ID/Case Name is 2 (in this case, as snag count per unit), but to filter that by month (preferably by current month) and by severity (like there were 2 High severity snags this month). Apologies for complicating this, not sure how to properly share information like this.

mangaus1111
Solution Sage
Solution Sage

Hi,

Sorry, having trouble following, can you post sample data as text and expected output?

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.