cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Using Calculated Measures to derive new Stats / Visualisations

Hi All,

I am trying to create few calculated measures and then want to re-use them to derive new Stats / Values. Here is an example from my end

Customer       Premium       Customer band (Derived Measure)        Org

C1                  100                                                                               O1

C1                  400                                                                               O2

C2                  50                                                                                 O1

C2                  375                                                                               O2

C3                  425                                                                               O2

I created a Calculated Measure named Customer Band which I would project in my Visualization along with Customer.

Assuming user selected Org = O2 then

Customer Band Derivation -> (total premium > 400) then High else Low

Customer       Customer Band

C1                   Low

C2                   Low

C3                   High

Using above result, I would like to project a new Visualisation using measure Customer Band

Customer Band                Count (DISTINCT Customers)

High                                 1

Low                                  2

Now Lets assume User selected Org = O1 then

Customer         Customer Band

C1                     Low

C2                     Low

Using Above result, I would like to project a new Visualisation using measure Customer Band

Customer Band               Count (DISTINCT Customers)

Low                                 2

How can this be achieved in Power BI. Please let me know you if more information is required.

2 ACCEPTED SOLUTIONS
Microsoft Employee

@srikanthguna

Such detailed description does make your requirement as clear as crystal! It is always a really good practice to ask a question in a forum.

For your case, I will create a auxiliary table and two measures as below. See more details in the attached pbix.

```HighLowCnt =
VAR summizedTbl =
SUMMARIZE ( 'Table', 'Table'[Customer], "totalPrem", SUM ( 'Table'[Premium] ) ),
"level", IF ( [totalPrem] > 400, "High", "Low" )
)
RETURN
COUNTROWS (
FILTER ( summizedTbl, [level] = LASTNONBLANK ( 'Level'[Level], "" ) )

Frequent Visitor

Thanks Eric for the solution. Please correct me if my understanding of the solution is incorrect ...

"summizedTbl" gets calculated / evaluated for each of the Level values "High" and "Low". If I have 10 different bands then the "summizedTbl" get calculated for all the Bands.

Would this slow down the performance of my PowerBI Report / Dashboard ???

2 REPLIES 2
Microsoft Employee

@srikanthguna

Such detailed description does make your requirement as clear as crystal! It is always a really good practice to ask a question in a forum.

For your case, I will create a auxiliary table and two measures as below. See more details in the attached pbix.

```HighLowCnt =
VAR summizedTbl =
SUMMARIZE ( 'Table', 'Table'[Customer], "totalPrem", SUM ( 'Table'[Premium] ) ),
"level", IF ( [totalPrem] > 400, "High", "Low" )
)
RETURN
COUNTROWS (
FILTER ( summizedTbl, [level] = LASTNONBLANK ( 'Level'[Level], "" ) )

Frequent Visitor

Thanks Eric for the solution. Please correct me if my understanding of the solution is incorrect ...

"summizedTbl" gets calculated / evaluated for each of the Level values "High" and "Low". If I have 10 different bands then the "summizedTbl" get calculated for all the Bands.

Would this slow down the performance of my PowerBI Report / Dashboard ???

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors