Forum Discussion

IamTDR's avatar
IamTDR
Icon for Responsive Resident rankResponsive Resident
1 year ago
Solved

Dax Help - Calculating Market Share

 

Trying to write a dax measure to calculate Market Share correctly.  The left handed side is correctly calulating Market Share at 3.1% (done in excel).  In Power BI, the Market Share is incorrectly being calculated as 1.5%.
My Market Share measure is 'Market Share = [PIU] / ABS([Total_Enrollment])
I need the Market Share measure to filter out the rows with no PIU data because Market Share is correct by row but not in total.
A work around I've been using involves using the filtering panel and filtering out PIU that is blank.  I'm thinking dax must have a better solution.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi IamTDR ,

     

    Maybe you can modify your formula like below:

    Market Share1 =
CALCULATE (
    SUM ( [PIU] ) / ABS ( SUM ( [Total Enrollment] ) ),
    FILTER ( 'Table', NOT ( ISBLANK ( [PIU] ) ) )
)

    Best Regards,
    Adamk Kong

     

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

     

5 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    1 year ago

    1.5% may not be the number you want, but it is the correct calculation of  [PIU] / ABS([Total_Enrollment]) at the total line

     

    How you get the answer you want in DAX depends on the table and data structure. If you post that info, I'm sure someone will help

    • IamTDR's avatar
      IamTDR
      Icon for Responsive Resident rankResponsive Resident
      1 year ago

      I have just two source tables (PIU & Total Enrollment) connected to a Dimension table for Programs.  Rather straight forward.
      Not every Program has PIU but Market Share should be calculated as PIU divided by Total Enrollment (but only include Total Enrollment for programs with PIU)

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    1 year ago

    In that case, you should be able to use black magic.  This assumes you have the project code coming from the project dim table in your visual

     

    =CALCULATE(sum(PIUTable[PIUValue])/sum(EnrollmentTable[EnrollmentValue]),PIUTable)

     

    Using the PIUTable name as a filter in this way inside calcuate has the effect of creating an inner join and hence filtering out everything that does not have a value for PIU

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi IamTDR ,

     

    Maybe you can modify your formula like below:

    Market Share1 =
CALCULATE (
    SUM ( [PIU] ) / ABS ( SUM ( [Total Enrollment] ) ),
    FILTER ( 'Table', NOT ( ISBLANK ( [PIU] ) ) )
)

    Best Regards,
    Adamk Kong

     

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