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
mrbajana
Helper III
Helper III

Total Numbers OrderID over or below average

I have a simple data set I need a mesure that counts(Distinct) by each Order ID , 1 if the sales is above the average and 0 if is below average.

 

Each order have many items, only I need the OrderID counts. The objective is to count how many orders are below or above the average sales in a single card or visual objetcts

 

2020-06-16_17h18_03.png

 

Thanks for your help.

 

Marco 

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

Please try this expression patter for a measure in a card visual

 

Orders Above Average =
VAR __avg =
    CALCULATE ( AVERAGE ( Table[Sales] ), ALLSELECTED ( Table ) )
RETURN
    COUNTROWS (
        FILTER ( VALUES ( Table[OrderID] ), CALCULATE ( SUM ( Table[Sales] ) ) > __avg )
    )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

1 REPLY 1
mahoneypat
Employee
Employee

Please try this expression patter for a measure in a card visual

 

Orders Above Average =
VAR __avg =
    CALCULATE ( AVERAGE ( Table[Sales] ), ALLSELECTED ( Table ) )
RETURN
    COUNTROWS (
        FILTER ( VALUES ( Table[OrderID] ), CALCULATE ( SUM ( Table[Sales] ) ) > __avg )
    )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.