Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
However my PowerBI report is keep showing the wrong result. (See Below)
The data structure is relatively straightforward.
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?
Solved! Go to Solution.
Hi @bubbletao
Sorry, by mistak, i upload the wrong picture in my last reply.
Updates here
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
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] )
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
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')
Here is the visual and axis/value I picked.
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |