Calculate cumulative percentage without date

[Reza]

Hi everyone,

I have searched this and tried so many combinations from various pages including here and other communities but none of them seems to fit with my requirements.

This is my model in Power Pivot:Power Pivot Model

 Sections Lookup looks like this:

Sections Lookup

The Sections looks like this:

Sections

The Section Count is a measure: 

Section Count:=SUM([Count])

The Percent calculated like this:

=DIVIDE([Count],SUM([Count]),0)

My Pivot Table look like this:

Pivot table with percent

The Section Count is the measure.

Now the problem.

I can create "% Running Total In" in Excel on the pivot table but this model is going to be used in Power BI desktop and there I don't have this option but % of Grand Total only. So I need to create this column on my model to look like this:

Require % Running Total Column

Please note that the Sections and Section Lookup are Views in my database and I just added Sections Lookup to the model for demonstration but don't need that in reality.

Any help would be extremely appreciated. 

Thanks

[MFelix]

Hi @Reza,

Add the following measure to your data:

% Running Total =
VAR Section_Selection =
    MAX ( Sections[Section] )
RETURN
    DIVIDE (
        CALCULATE ( [Section Count]), Sections[Section] <= Section_selection ),
        CALCULATE ( [Section Count] ), ALLSELECTED ( Sections[Section] ) )
    )

Should give the expected result.

Regards,

MFelix

[Reza]

Hi @MFelix,

Please correct me if I am wrong. I don't think so I can use that calculation in Power Pivot. So I was going to crate a "Select_Selection" measure and then use that in the running total measure. But is Max( Sections[Section] ) correct as it is a string? I get error.

And it seems there are extra ) in the CALCULATE after [Section Count] but even with removing them does not work. I get error "A function CALCULATE has been used in True/False expression that is used as a table filter expression."

Thanks

[MFelix]

Hi @Reza,

You can use the VAR expression exacly as is in my formula:

Regards,

MFelix

[Reza]

Hi @MFelix,

I think there is a misunderstanding. I need to add this calculated column to my model as I mentioned in my original post.

"I can create "% Running Total In" in Excel on the pivot table but this model is going to be used in Power BI desktop and there I don't have this option but % of Grand Total only. So I need to create this column on my model to look like this".

And the formula does not work on pivot table.

Thanks,

Reza

---

[MFelix]

Hi @Reza,

Then it's even easier, you add your model as is in the PBI and the add the measure in the BPI and addd it to your visuals, no need to add an additional column to your model calculating the % PBI does that for you.

Just an additonal question why are you make all the summarization im power pivot and don't do it o in PBI? if you upload your data model and do all measures in PBI it will be more light on the BPIX file.

Regards,

MFelix

[Reza]

Hi @MFelix,

Thanks but the requirement still remain the same. I need this in the moel as the excel file will be handed to the customer along the Power BI stuff. And both should be the same. The excel file will be in OneDrive and the Power BI use that as dataset and updated on schedule. 

So my question still remain unansewerd. 

How can I achieve that as a calculated column in the model? Even if I need the Running Total alone wihtout percentage?

Regards,

Reza

[MFelix]

Hi @Reza,

Sorry for insisting on this but I want to help you in the best way I can.

You have your data model in Power Pivot and will have a result in a Pivot table as you presented?

Do you want to have the Pivot Table that you show in the image as a table of your model or as a Pivot table on the Excel?

You want to have the exact same result in Power BI?

Regards,

MFelix

[Reza]

Hi @MFelix,

Really appreciate your help.

Let's forget about the excel. What I shared was how data look like on Pivot Table. But I am not creating any pivot table in the excel. I am just using the excel to create my model on the Power Pivot and use that as dataset in my Power BI. 

Now you mentioned I can use my model as it is and in the Power BI show the % of Running Total? Is that correct? Because I can just see the % of Grand Total in the Power BI. Or if I have to create a Mesure in the Power BI that does the job. 

Can you please look at the calculation you sent me becIause that did not work. If that work I will add it to PBI as a new measure. I get error on the MAX( Sections[Section] ) as Section[Section] is string.

Regards,

Reza

[MFelix]

Hi @Reza,

As I said if you create a measure on PBI with the formula I gave you that will do the trick in PBI, the only part that you have to take attention is that you must use the Section(Section Table) in your table visual and not the Section (on any other table) because my measure is based on the relationship you present on your image.

Regards,

MFelix

[Reza]

Hi @MFelix,

Yes I am using Sections table to get Section. And this is what Sections table it is:

Sections

Are you pointing to the above table? 

As you see the Section in this table is string so MAX( Sections[Section] ) and Sections[Section] <= Section_selection in the calculation are not valid.

Regards,

Reza

[MFelix]

Hi @Reza,

In this case you need to do it at the Sections Lookup table because is where you have th eunique values.

Measure would be:

% Running Total =
VAR Section_Selection =
    MAX ( Sections Lookup [Section] )
RETURN
    DIVIDE (
        CALCULATE ( [Section Count]), Sections Lookup [Section] <= Section_selection ),
        CALCULATE ( [Section Count] ), ALLSELECTED ( Sections Lookup [Section] ) )
    )

Regards,

MFelix

[Reza]

Hi @MFelix,

The Sction in Sections Lookup is also string.

Sections Lookup

Regards,

Reza 

[MFelix]

No problem,

In DAX you can use the MAX functions with string also not only with numbers.

Regards,

MFelix

[Reza]

Hi @MFelix,

Ok, we are getting there. 

What about this?

Sections Lookup [Section] <= Section_selection

That compare string with a number?

Regards,

Reza

[MFelix]

Hi @Reza,

The Section_Selection is a Variable  that we added in the beginning of the expression that returns the MAX of the Sections so when you come to that part of the formula you are comparing text with text again < = will also work with strings this is based on the DAX context part you are basicly comparing the row value with the previous row values,

Regards,

MFelix

[Reza]

Hi @MFelix,

Ran the calculation. As you see I get error on MAX.

Calculation error in measure 'Sections'[% Running Total]: 
The function MAX takes an argument that evaluates to numbers or dates and cannot work with values of type String.

I am on Office Professional Plus 2016 if that helps.

Regards,

Reza

[MFelix]

HI @Reza,

as I said in  PBI the formulas works as is, however you can change the 'Sections Lookup [Section]' to 'Sections Lookup [ID]' .

shold do the trick in power pivot.

[Reza]

Hi @MFelix,

I used ID but the result is exactly like my percentage column and not cummulative.

Regards,

Reza

[MFelix]

Try to change the ALLSELECTED by ALL.

Mfelix

[Reza]

Hi @MFelix,

Thanks for helping me on this issue.

I was wondering does it help if I send you a mock up database and the excel file privately?

That way you would see the whole data and the fact this is a many to many relation could help.

Regards,

Reza