Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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