topic Re: Count mesure under condition in Developer

Count mesure under condition

Anonymous — Thu, 19 Oct 2017 11:47:38 GMT

hello, I have a table consumption as follow:

Transformer DAY KVA%
1 15102017 80
1 16102017 40
1 17102017 60
2 15102017 40
2 16102017 20
2 17102017 100
3 15102017 60
3 16102017 100
3 17102017 120
4 15102017 140
4 16102017 70
4 17102017 90
5 15102017 10
5 16102017 20
5 17102017 90

I want first to get the average of KVA% column for each transformer. I did the below mesure:

AvgKVA%=AVERAGEX('Transformer',CALCULATE(AVERAGE('Transformer'[KVA%]))) and I got the below info:

Transformer AvgKVA%
1 60
2 53,33
3 93,33
4 100
5 40

Now I would like to make cake graph that tells me AvgKVA%>80 and 50<AvgKVA%<=80 and AvgKVA%<=50.
In the example will be:

We have 2 transformer with AvgKVA%>80, 2 transformer with AvgKVA% between 50 and 80 and 1 transformer AvgKVA%<=50.
Then I would like to represent as a sectorial graph saying

40% -> KVA% >80
40% -> 50<Average of KVA%<=80
20% -> KVA%<=50

Re: Count mesure under condition

Eric_Zhang — Fri, 20 Oct 2017 06:59:48 GMT

@Anonymous wrote:

hello, I have a table consumption as follow:

Transformer DAY KVA%
1 15102017 80
1 16102017 40
1 17102017 60
2 15102017 40
2 16102017 20
2 17102017 100
3 15102017 60
3 16102017 100
3 17102017 120
4 15102017 140
4 16102017 70
4 17102017 90
5 15102017 10
5 16102017 20
5 17102017 90

I want first to get the average of KVA% column for each transformer. I did the below mesure:

AvgKVA%=AVERAGEX('Transformer',CALCULATE(AVERAGE('Transformer'[KVA%]))) and I got the below info:

Transformer AvgKVA%
1 60
2 53,33
3 93,33
4 100
5 40

Now I would like to make cake graph that tells me AvgKVA%>80 and 50<AvgKVA%<=80 and AvgKVA%<=50.
In the example will be:

We have 2 transformer with AvgKVA%>80, 2 transformer with AvgKVA% between 50 and 80 and 1 transformer AvgKVA%<=50.
Then I would like to represent as a sectorial graph saying

40% -> KVA% >80
40% -> 50<Average of KVA%<=80
20% -> KVA%<=50

@Anonymous

You need to create an auxiliary table and create a measure as below. See more details in the attached pbix file.

portion =
VAR summizedTBL =
    SUMMARIZE (
        Transformer,
        Transformer[Transformer],
        "Average of KVA", AVERAGE ( Transformer[KVA%] )
    )
VAR summizedTBL2 =
    ADDCOLUMNS (
        summizedTBL,
        "range", SWITCH (
            TRUE (),
            [Average of KVA] > 80, "KVA% >80",
            [Average of KVA] <= 50, "KVA%<=50",
            "50<Average of KVA%<=80"
        )
    )
RETURN
    COUNTROWS ( FILTER ( summizedTBL2, [range] = MAX ( range[range] ) ) )

Re: Count mesure under condition

Anonymous — Fri, 20 Oct 2017 10:51:58 GMT

That is great. Thanks a lot.

Re: Count mesure under condition

Anonymous — Thu, 26 Oct 2017 13:34:48 GMT

just regarding to this topic, I have 2 questions.

How can I do to give to users to change the nondition, it means that is not going to be like this:

40% -> KVA% >80
40% -> 50<Average of KVA%<=80
20% -> KVA%<=50

It will be

KVA% >x
y<Average of KVA%<=x
KVA%<=z

So I can gice user to input x, y and z. Is there any way to do it?

The 2nd one, is that in the report that you sent, if i select a section in the pie chart, The tble that you did with transformer and Average of KVA% show the same thin when it should show the transformers with the section of pie chart selected. I tried to do a relation between range table and Transformer table using a range, but it looks that is not possible doing with a mesure,