Count number of raw in a calculated column

[faacq]

Hi,

In my main calculated column I display % usage for my products. I have 4 categories that describe how much the products are used (ex. if %usage => 60 , average etc..).
I created a visualization that show the products and these 4 categories.

I would like to know the number of products in each category. How can I create a dax query that address this scenario?
My goal is to create a stacked column chart, which display the number of products in each usage category

Thank you for the help

[v-haibl-msft]

@faacq

In this scenario, you need to create Column but not Measure here. You can create two columns with correct relationship between your three tables as below.

(%) = 
CALCULATE (
    SUM ( 'Test-data'[Test hours] ),
    ALLEXCEPT ( 'Test-data', 'Test-data'[Tester], 'Test-data'[Month] )
)
/ CALCULATE ( SUM ( Baseline[Hours (Max)] ) )

(cat) = 
IF (
    'Test-data'[(%)] = 0,
    "None",
    IF (
        'Test-data'[(%)] <> 0,
        IF (
            'Test-data'[(%)] <= 0.69,
            "Bad",
            IF (
                'Test-data'[(%)] >= 0.70,
                IF (
                    'Test-data'[(%)] <= 0.99,
                    "Average",
                    IF ( 'Test-data'[(%)] = 1, "OK", IF ( 'Test-data'[(%)] >= 1.001, "Very Good" ) )
                )
            )
        )
    )
)

If you still cannot get the expected result, I’d like to see the sample data of “Date dim” and “Baseline” table.

Best Regards,

Herbert

[faacq]

Hi Herbert

I tried your formulas in my test model, I think we are almost archive what I needed.

I still have two things that are not properly working, the first one regard the (cat) formula.

It seems that the IF statement is not able to catch the value that is like 100%, as you can see in the print screen the (%) formula return the correct value, but the raw is categorized as "bad" (<= 0.69)

The other things that I’m struggling to make it work, is the fact that tester which I have in the (cat) “none”, are counted in the graph but they are not displayed in the table positioned on the right side.

This behavior is probably connected to the fact that in the (%) formula, I filtered out all the entries that are not categorized as “Charge”, and "none" is the category that I use to group all the tester that have 0 hours categorized as “Charge”.

Do you have any idea how can make the entries that have no value in the (%) column appear in the tester column?

To complete the description provide you the information about the date dim and the baseline table.

The relationships between my tables are:

The baseline table is used to track the max amount of hours that the tester can run their workload. Baseline table:

I generate my Date dim using the query published in this blog:

http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

Thank you for your help

[v-haibl-msft]

@faacq

Thanks for your detailed information. I tried to make my dataset and relationship similar with yours. But I didn’t see the two problems you mentioned above.

I’ve uploaded my .pbix file here. Could you please take a look at it? Please let me know if there is any difference with yours.

Best Regards,
Herbert

[v-haibl-msft]

@faacq

According to your description, I create some sample data as below. Both of “% usage” and “Categories” are calculated columns.
If it is similar with your actual data, you can get the expected stacked column char without any other dax queries as following screenshot.
If your data table is different from this one and you still can’t get the expected result. Please provide some sample data so that we can give the exact dax query.

[faacq]

Hi,

I haven't properly describe my scenario. My apologies. In my model I used Measure and not Column.

Probably for this reason I'm not able to use the Measure in the Shared axis.

I have create a test model which reflect my data.

My dataset can be summarized as follow.

In order to calculate the %test I wrote this DAX, I have one query for each month

(%) Januar = CALCULATE (sum('Test-data'[Test hours]),'Date dim'[MonthOfYear] = "1")/ CALCULATE(sum('Baseline'[Hours (Max)]),'Baseline'[Month nr.] = "1")

In order to assign evaluate the result and assign a category I created a new measure

(cat) Januar = IF ([(%) Januar] = 0 , "None", IF([(%) Januar] <> 0, IF([(%) Januar] <=0.69 ,"Bad",IF([(%) Januar] >=0.70, IF([(%) Januar] <=0.99 ,"Average", IF([(%) Januar] = 1 ,"OK", IF([(%) Januar] >= 1.001 ,"Very Good")))))))

I endup with the following result:

My goal is to create a stacked chart, where I can visualize the number of testers for each category. This for each month.

Thank you