Creating measure to calculate percentage

[JoshP11]

Hello,

Wondering if anyone could help with creating a measure(s) to help calculate something.

Essentially, I want to know what percentage of 'Names' from 'Invoice Period' "November, December, January", have a total sum across those periods, of 0 for 'Data Usage', and 0 for 'Minutes Used'.

I'm guessing it would need to be done across 2 or 3 measures with various variables however still relatively new to PBI!

Any help would be appreciated!

Thanks,

Josh

[Anonymous · Accepted solution]

Hi @JoshP11 ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the measure to calculate Data percentage.

Data_percentage = 
VAR Filtere_zero = CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALLEXCEPT('Table','Table'[Name]),
        'Table'[Invoice Period] IN {"September", "October", "November"} && 'Table'[Data Usage] = 0
    )
)
VAR month_rows = CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALLEXCEPT('Table', 'Table'[Name]),
        'Table'[Invoice Period] IN {"September", "October", "November"}
    )
)
VAR PercentageZeroUsage = 
DIVIDE(
    Filtere_zero,
    month_rows
)
RETURN
FORMAT(PercentageZeroUsage, "0.0%")
//PercentageZeroUsage

3.Create the measure to calculate Minute percentage.

Minute_percentage = 
VAR Filtere_zero = CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALLEXCEPT('Table','Table'[Name]),
        'Table'[Invoice Period] IN {"September", "October", "November"} && 'Table'[Minutes Used] = 0
    )

)
VAR month_rows = CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALLEXCEPT('Table','Table'[Name]),
        'Table'[Invoice Period] IN {"September", "October", "November"}
    )

)
VAR PercentageZero = 
DIVIDE(
    Filtere_zero,
    month_rows
)
RETURN
FORMAT(PercentageZero, "0.0%")

4.Drag the measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

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

[Wilson_]

Hi Josh,

Please check out the pinned thread in the forum to learn how to provide sufficient details to get your question answered. 🙂

[JoshP11]

Hi @Wilson_ thanks for the tip!

I've had a little read and im guessing all that is missing from my request for assistance is some sample data? I will work on attaching that to the post. 

if you can recommend anything else please let me know!

cheers

[Wilson_]

Hi JoshP11,

Yes, ideally in a mock pbix file. Otherwise, some sample data and what your model and expected result looks like would be super helpful. Thanks! 😄

[JoshP11]

@Wilson_  for some reason it won't allow me to upload the files to this comment?

Hopefully this screenshot gives a better idea on what I mean.

I'd basically like to a way to calculate what percentage of people, have a total sum of 0 for data AND a total sum of 0 for minutes, covering the invoice period of November, December, January.