Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count mesure under condition

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

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee


@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.

Capture.PNG

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] ) ) )

Capture.PNG

View solution in original post

3 REPLIES 3
Eric_Zhang
Employee
Employee


@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.

Capture.PNG

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] ) ) )

Capture.PNG

Anonymous
Not applicable

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,

Anonymous
Not applicable

That is great. Thanks a lot.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.