cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
srikanthguna
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
Eric_Zhang
Microsoft
Microsoft

@srikanthguna

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

 

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

Capture.PNG

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

HighLowLvl = IF(SUM('Table'[Premium])>400,"High","Low")

Capture.PNGCapture.PNG

View solution in original post

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 ???

View solution in original post

2 REPLIES 2
Eric_Zhang
Microsoft
Microsoft

@srikanthguna

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

 

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

Capture.PNG

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

HighLowLvl = IF(SUM('Table'[Premium])>400,"High","Low")

Capture.PNGCapture.PNG

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 ???

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.