Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a measure and I need a way to sum up the value the measure produces.
I have come up with the example below
Report Table that I would produce in Power BI
==============================
Number of channels
per Customer Measure 1 Measure 2 Measure 3
1 1 1 .5 <== Correct Value
2 1 2 .33 <== Correct Value
3 1 0 1 <== Correct Value
Total 3 3 1.83 <== this is the value I am looking for on a total line
1.00 . <== this is what I am getting but not what I want
Here is the Raw Data
RawTable
=======
Customer ID Channel Age of Data Number of channels Customer Type Customer ID Customer ID
Days per Customer A B
123 web 0 3 A 123 <blank>
123 web 2 3 A 123 <blank>
123 email 5 3 A 123 <blank>
123 assisted 3 3 A 123 <blank>
224 web 0 2 A 224 <blank>
224 email 5 2 A 224 <blank>
664 email 4 1 A 664 <blank>
723 assisted 3 1 B <blank> 723
724 web 0 2 B <blank> 724
724 email 5 2 B <blank> 724
824 web 0 2 B <blank> 824
824 email 5 2 B <blank> 824
Measure 1 = distinct count of customers for type A
Measure 1 = DISTINCTCOUNT('RawTable'[Customer ID A])
Measure 2 = distinct count of customers for type B
Measure 2 = DISTINCTCOUNT('RawTable'[Customer ID B])
Measure 3 = distinct count of customers for type A divided by total number of customers
Measure 3 =
DIVIDE(
Measure 1
,CALCULATE(Measure 1+Measure 2)
,0)
Now I need to get to a sum of Measure 3
in the example I have given I would need to get to a total of 1.83
Now this would need to be dynamically worked out so that
when I add data slicers for age, or type of channel the sum of
measure 3 would need to be worked out
I cant get this to work .
Please help
While I could not fully resolve this issue, I have got to a place that at least shows the sum of the measures.
I created a table inside the power bi report itself, as follows
SUM MeasureTable =
// Provide starting point for loop needed to build table
VAR __MaxNumberOfChannels
= CALCULATE('*Measures'[MaxNumberOfChannels])
// Generate a "loop table", this will emulate a for loop for i=1 to some number
VAR __loopTable
= GENERATESERIES(1,__MaxNumberOfChannels)
// Add in our calculated sum, emulating calculations done as iterations over the loop
VAR __loopTable1 =
ADDCOLUMNS
(__loopTable
,"Number of channels per Customer" , [Value]
,"Measure 1"
, CALCULATE('*Measures'[Measure 1]
,'RawTable'[Number of channels per Customer ]=EARLIER([Value] )
)
,"Measure 2"
, CALCULATE('*Measures'[Measure 2]
,'RawTable'[Number of channels per Customer ]=EARLIER([Value] )
)
,"Measure 3"
, CALCULATE('*Measures'[Measure 3]
,'RawTable'[Number of channels per Customer ]=EARLIER([Value] )
)
)
RETURN
__loopTable1
now this does produce the results correctly, but since the data is calculated as its loaded into the table, the results are not effected by any filtering.
Maybe there is a way to build the table to reload each time you change the value on a data slicer for example, but I dont now how
not sure if this approach gets me closer or further aways from my desired final result
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
2 | |
2 | |
1 | |
1 |