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
bubbletao
Frequent Visitor

Create a histogram from calculated measure and joined tables

I am attemping to create an histogram to show the distribution of a metric(Bounce Rate) from number of customers.

 

The end goal can be shown  in the following example excel chart, and it needs to be dynamic based on the slicer/filter

2019-09-20 10_44_59-Book1 - Excel.png

However my PowerBI report is keep showing the wrong result. (See Below)

Wrong Histogram.png

The data structure is relatively straightforward.

data structure.png

The measure itself is a calculated measure, as well as the number of customers. 

BounceRate = DIVIDE(sum(SiteTraffic[Bounces]),sum(SiteTraffic[Sessions]))
CountOfCustomer = COUNTROWS(DIM_Customer)

I created the following calculated columns to use as the bin of the histogram: 

BounceRateRange = SWITCH(True(), [BounceRate] < 0.3, "<0.3",[BounceRate] < 0.7, "0.3-0.7", ">0.7")

Can anyone help me what's wrong with my calculation?  

1 ACCEPTED SOLUTION

Hi @bubbletao 

Sorry, by mistak, i upload the wrong picture in my last reply.

Updates here

Capture22.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

Hi @bubbletao 

The error may cause by "BounceRateRange" is a calcualted column, but [BounceRate] is a measure.

I find a workaround

1. create a new table

Capture2.JPG

 

2. create measures

BounceRate =
CALCULATE (
    SUM ( SiteTraffic[Bounces] ),
    FILTER (
        ALLSELECTED ( SiteTraffic ),
        SiteTraffic[customer id] = MAX ( SiteTraffic[customer id] )
    )
)
    / CALCULATE (
        SUM ( SiteTraffic[Sessions] ),
        FILTER (
            ALLSELECTED ( SiteTraffic ),
            SiteTraffic[customer id] = MAX ( SiteTraffic[customer id] )
        )
    )

CountOfCustomer = DISTINCTCOUNT(SiteTraffic[customer id])

BounceRateRange = SWITCH(True(), [BounceRate] < 0.3, "<0.3",[BounceRate] < 0.7, "0.3-0.7", ">0.7")
Measure =
VAR m =
    SUMMARIZE (
        SiteTraffic,
        SiteTraffic[customer id],
        "m1", CALCULATE (
            IF (
                MIN ( 'Table'[min] ) <= [BounceRate]
                    && (
                        MAX ( 'Table'[max] ) >= [BounceRate]
                            || MAX ( 'Table'[max] ) = BLANK ()
                    ),
                1
            )
        )
    )
RETURN
    COUNTX ( m, [m1] )
Capture2.JPG
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-juanli-msft .  Can you share highlevel why we can use this workaround to bypass the calcualted columns/measure limitation?

 

Also can you explain briefly what the following measure is used for?  What is the the name of this measure?  Where shall I put this measure on the histogram visual?

Measure =
VAR m =
    SUMMARIZE (
        SiteTraffic,
        SiteTraffic[customer id],
        "m1", CALCULATE (
            IF (
                MIN ( 'Table'[min] ) <= [BounceRate]
                    && (
                        MAX ( 'Table'[max] ) >= [BounceRate]
                            || MAX ( 'Table'[max] ) = BLANK ()
                    ),
                1
            )
        )
    )
RETURN
    COUNTX ( m, [m1] )

 

Hi @bubbletao 

Sorry, by mistak, i upload the wrong picture in my last reply.

Updates here

Capture22.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-juanli-msft  Thanks for the sample file. 

 

I am still having a hard time understanding the logic of the measure.  Can you provide a bit more context, why this works etc?

Measure = var m=SUMMARIZE(SiteTraffic,SiteTraffic[customer id],"m1",CALCULATE(IF(MIN('Table'[min])<=[BounceRate]&&(MAX('Table'[max])>=[BounceRate]||MAX('Table'[max])=BLANK()),1))) return COUNTX(m,[m1])

In the meantime, I have found out using a Python Script custom visual can bridge the gap.  The catch here is the python visual is much slower than the native column chart, especially applying slicer. 

dataset = dataset.dropna()
import matplotlib.pyplot as plt
import seaborn as sns
sns.distplot(dataset[Bounce Rate],bins=15, color='k')
bubbletao
Frequent Visitor

Here is the visual and axis/value I picked. 

 

Wrong Histogram.png

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.