Dax Help - Calculating Market Share

[IamTDR]

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.

[v-kongfanf-msft · Accepted solution]

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.

[MattAllington]

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

[MattAllington]

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]

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)

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.