Skip to main content
cancel
Showing results for 
Search instead 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

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

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

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

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.