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