Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]))))
Solved! Go to 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
See if this helps:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
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.
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:
HI @Alessandra,
Maybe you can try to share your pbix file for furture test.
Regards,
Xiaoxin Sheng
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
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
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
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
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
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |