Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alessandra
Helper I
Helper I

How to make the grand total row full of 100%

Hi,

 

I have a measure in Power BI, it's all ok, but I want to force the grand total so it can be 100%.

Any ideas?

 

Here is the measure: 

 

Measure = IF(CALCULATE(SUM(table[ValChannel]),ALLEXCEPT(table,TABLE[Channel]))=0,(SUMX(
SUMMARIZE(TABLE,TABLE[IdBussiness],TABLE[Date],
"MaxSubtotal", MIN(TABLE[S])),[MaxSubtotal]) / CALCULATE(SUMX(
SUMMARIZE(TABLE,TABLE[IdBusiness],TABLE[Date],
"MaxSubtotal", MAX(TABLE[D])),[MaxSubtotal]),ALLSELECTED(TABLE[SEG]),ALLSELECTED(TABLE[FA]),ALLSELECTED(TABLE[Brand]),ALLSELECTED(TABLE[PRES]),ALLSELECTED(TABLE[TAM]))))

1 ACCEPTED SOLUTION

Hi @Alessandra,

 

Based on your formula, it seems like you are try to calculate on summarized 'min total ' value and 'max total' value who filtered by current row contents, right?

 

If this is a case, you can take a look at below measure if it suitable for your requirement.

 

Sample:

SSN =
VAR summaried =
    SUMMARIZE (
        ALLSELECTED ( table ),
        table[CodigoNegocio],
        table[FechaD],
        "MinSubtotal", MIN ( table[SSTPESO] ),
        "MaxSubtotal", MAX ( table[DSTPESO] )
    )
RETURN
    IF (
        HASONEVALUE ( table[SEGMENTOS] ),
        DIVIDE (
            SUMX (
                FILTER ( summaried, [SEGMENTOS] = SELECTEDVALUE ( table[SEGMENTOS] ) ),
                [MinSubtotal]
            ),
            SUMX (
                FILTER ( summaried, [SEGMENTOS] = SELECTEDVALUE ( table[SEGMENTOS] ) ),
                [MaxSubtotal]
            ),
            0
        ),
        1
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

11 REPLIES 11
Greg_Deckler
Super User
Super User

See if this helps:

 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler,

 

The Hasonfilter doesn't help me because I want the grand total row always be 100% whether it has or not a filter.

 

You can try using ISFILTERED or some similar function like that.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler,

 

I used the function HANSONEFILTER, and my measure is:

 IF(HASONEVALUE(E1_T0_C0509_P0001_F1[BRAND]), MyMeasure,1)

BUT, when I filter only one brand, the grand total stops being 100%

( When I filter more than 1 brand, there is no problem, like in the second picture)

Is there a way it can change that? 

 

Here is what I have in Power BI:

Captura1.JPGCaptura2.JPG

HI @Alessandra,

 

Maybe you can try to share your pbix file for furture test.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft,


I just want to know if there's a function that works as an index or something like that, so I can calculate the measure saying for example, that the grand total should be in percentages and the subtotal in absolute. I'm attaching an excel so you can visualize what I'm trying to say.

For example, in tableau, I use the function Attr, this function filters the view ( atr1,atr2,atr3 are atributes of a product, and I have them sorted in a hierachy that I've created in powerBI

 

Captura2.JPGCaptura.JPG

 

 

https://drive.google.com/file/d/1kiRH4OYzfc9NmKn8zR8uvl42k7fy36Hz/view?usp=sharing

Thanks for your time

Hi @Alessandra,

 

What query you use? I feel confused with some functions which in your screenshots.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft,

Thanks for answering.

 

This is the code I'm using, I've tried with HASONEVALUE function but it only has one argument, I can't add another atribute from my hierarchy or when I change the order of the hierarchy the query turn wrong. 

SSN = IF(HASONEVALUE(table[SEGMENTOS]),SUMX(
SUMMARIZE(table,table[CodigoNegocio],table[FechaD],
"MaxSubtotal", MIN(table[SSTPESO])),[MaxSubtotal]) / CALCULATE(SUMX(
SUMMARIZE(table,table[CodigoNegocio],table[FechaD],
"MaxSubtotal", MAX(table[DSTPESO])),[MaxSubtotal]),ALLSELECTED(table[GSK_SEGMENTOS]),ALLSELECTED(table[FABRICANTES]),ALLSELECTED(table[MARCAS]),ALLSELECTED(table[PRESENTACIONES]),ALLSELECTED(table[TAMANOS_1S]),ALLSELECTED(table[TAMANOS_2S]),ALLSELECTED(table[VARIEDADES_1S]),ALLSELECTED(table[VARIEDADES_2S])),1)

Thanks in advance for your reply

 

PS: The functions on the screen are from tableau, I uploaded them in case anyone know how to do the same as tableau, but in Power BI

Hi @Alessandra,

 

Based on your formula, it seems like you are try to calculate on summarized 'min total ' value and 'max total' value who filtered by current row contents, right?

 

If this is a case, you can take a look at below measure if it suitable for your requirement.

 

Sample:

SSN =
VAR summaried =
    SUMMARIZE (
        ALLSELECTED ( table ),
        table[CodigoNegocio],
        table[FechaD],
        "MinSubtotal", MIN ( table[SSTPESO] ),
        "MaxSubtotal", MAX ( table[DSTPESO] )
    )
RETURN
    IF (
        HASONEVALUE ( table[SEGMENTOS] ),
        DIVIDE (
            SUMX (
                FILTER ( summaried, [SEGMENTOS] = SELECTEDVALUE ( table[SEGMENTOS] ) ),
                [MinSubtotal]
            ),
            SUMX (
                FILTER ( summaried, [SEGMENTOS] = SELECTEDVALUE ( table[SEGMENTOS] ) ),
                [MaxSubtotal]
            ),
            0
        ),
        1
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft

Thank you so much !! 

I'm more insterested in some function that would work like "dimension" in qlikview, here is an example:

https://drive.google.com/file/d/1hQe6dmCu2DP5c4tK34cHyFbrk6SoAL-b/view?usp=sharing

 

And here is what I have in Power BI

https://imgur.com/a/bQ9VS

 

Instead of the percetages of the grand total (dimension1)  I'd like to do a conditional so it can be 100%

IF "Dimension" = 1 THEN 100 ELSE SUM(Sales) END

 

 

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.