cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srikanthguna Frequent Visitor
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

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Using Calculated Measures to derive new Stats / Visualisations

@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

srikanthguna Frequent Visitor
Frequent Visitor

Re: Using Calculated Measures to derive new Stats / Visualisations

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
Moderator Eric_Zhang
Moderator

Re: Using Calculated Measures to derive new Stats / Visualisations

@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

srikanthguna Frequent Visitor
Frequent Visitor

Re: Using Calculated Measures to derive new Stats / Visualisations

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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 40 members 1,095 guests
Please welcome our newest community members: