cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ineedham Regular Visitor
Regular Visitor

How to create a measure calculating Cumulative Count by Year based on a specific value

I am trying to create a measure that returns the cumulative total of Pro licenses each year. The data looks like the table on the left, and I want it to return the values on the right:

 

pro license table.jpg

 

I’m able to create the following measure:

 

Cumulative Licenses =
CALCULATE (
    COUNTX ( 'Power BI Licenses', [License Status] = "Pro" ),
    FILTER (
        'Power BI Licenses',
        [Pro License Start Year]
            <= MAX ( [Pro License Start Year] )
    )
)

 

…but it won’t work in the line chart visual.  I get this error:

 

MdxScript(Model) (4, 86) Calculation error in measure Power BI Licenses'[Cumulative Licenses]: The function COUNTX cannot work with values of type Boolean.

 

I tried it with COUNTAX instead of COUNTX, and it works with the visual, but doesn’t return the running totals.

 

Also not having luck with 

 
COUNTROWS(
              FILTER(
                   SUMMARIZE (
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to create a measure calculating Cumulative Count by Year based on a specific value

Hi @ineedham ,

For your requirement, please try the measure below.

 

Measure = 
IF (
    MAX ( 'Table1'[License Status] ) = "",
    BLANK (),
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            ALL ( Table1 ),
            'Table1'[Pro License start year] <= MAX ( 'Table1'[Pro License start year] )
                && 'Table1'[License Status] <> ""
        )
    )
)

Here is the output.

 

Capture.PNG

You also could have a reference of my attachment.

If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: How to create a measure calculating Cumulative Count by Year based on a specific value

Hi @ineedham ,

For your requirement, please try the measure below.

 

Measure = 
IF (
    MAX ( 'Table1'[License Status] ) = "",
    BLANK (),
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            ALL ( Table1 ),
            'Table1'[Pro License start year] <= MAX ( 'Table1'[Pro License start year] )
                && 'Table1'[License Status] <> ""
        )
    )
)

Here is the output.

 

Capture.PNG

You also could have a reference of my attachment.

If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ineedham Regular Visitor
Regular Visitor

Re: How to create a measure calculating Cumulative Count by Year based on a specific value

That worked.  Thank you!