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 am new to PowerBi and have spent a considerable amount of time struggling with this problem. I am hoping that someone here can tell me if what I am attempting is even possible.
I have the following table which I requirement to use various sliders on (it currently has two slicers to make it smaller for the question).
Is it possible to create a Measure that will provide a running total of the [Count of Volume] field, in the order from greatest to least, or in the order of the [clc_Rank] measure as seen above? My end goal is to have a cummulative percentage, but I can't get past this one step.
The code that I am currently working with for the [clc_RunningTotal] measure is as follows:
clc_RunningTotal = CALCULATE( COUNTA('vmFNC_DATA_DAILY_RTH()'[Volume]), FILTER( ALLSELECTED('vmFNC_DATA_DAILY_RTH()'[Volume (bins)]), ISONORAFTER('vmFNC_DATA_DAILY_RTH()'[Volume (bins)], MAX('vmFNC_DATA_DAILY_RTH()'[Volume (bins)]), DESC) ) )
The code gives a running total, but orders it according to the [Volume (bins] field. PowerBi won't accept my [clc_Rank] measure to order the table.
Is there a way to do this?
thank you,
Solved! Go to Solution.
Hi @vmonkey
This will do it
clc_RunningTotal_NEW3 = VAR CurrentCount = SUM ( 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount] ) RETURN SUMX ( FILTER ( ALL ( 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ), CALCULATE ( SUM ( 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount] ) ) >= CurrentCount ), CALCULATE ( SUM ( 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount] ) ) )
Hi @vmonkey
Add this measure. Modify the tablename (Table1)
Running_Total= VAR CurrentCount = VALUES ( Table1[Count of Volume] ) RETURN IF ( HASONEVALUE ( Table1[Count of Volume] ), SUMX ( FILTER ( ALL ( Table1 ), Table1[Count of Volume] <= CurrentCount ), CALCULATE ( VALUES ( Table1[Count of Volume] ) ) ) )
Hi Zubair_Muhammad,
thank you for your response.
The [Count of Volume] field is derived by me placing the [Volume] field into the 'Values' section for the visualization and then right clicking and selecting 'Count'. For the purposes of the new code, I recreated this field using the following calculated column:
clc_COL_VolumeCount = COUNTA([Volume])
I then tried substituting this calculated column in [Count of Volume]'s place in the code I but recieved the folloing result:
My appologies. I made an error in my last post. I accidentally was using the [Volume] field in that one.
The table looks like this with the [clc_RunningTotal_New] measure using the [clc_COL_VolumeCount] calculated column:
clc_RunningTotal_NEW = VAR CurrentCount = VALUES ( 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount]) RETURN IF ( HASONEVALUE ( 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount] ), SUMX ( FILTER ( ALL ( 'vmFNC_DATA_Daily_RTH()' ), 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount] <= CurrentCount ), CALCULATE ( VALUES ( 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount] ) ) ) )
Hi @vmonkey
Try this code.
clc_RunningTotal_NEW = VAR CurrentCount = SUM ( 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount] ) RETURN IF ( HASONEVALUE ( 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount] ), SUMX ( FILTER ( ALL ( 'vmFNC_DATA_Daily_RTH()' ), CALCULATE ( SUM ( 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount] ) ) <= CurrentCount ), CALCULATE ( SUM ( 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount] ) ) ) )
no - identical result unfortunately; each row has '6699' in it.
I'll clean up the file a bit and post it this afternoon for you to take a look at.
Really appreciate the help!
Here you go!
The numbers will be different as I have removed a considerable amount of the data and unrelated fields, but everything else is there!
thank you,
Hi @vmonkey
This will do it
clc_RunningTotal_NEW3 = VAR CurrentCount = SUM ( 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount] ) RETURN SUMX ( FILTER ( ALL ( 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ), CALCULATE ( SUM ( 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount] ) ) >= CurrentCount ), CALCULATE ( SUM ( 'vmFNC_DATA_DAILY_RTH()'[clc_COL_VolumeCount] ) ) )
Really appreciated your help! thank you very much
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |